Cumulative distinct count filtered by last value - DAX

0 votes

I have a dataset:

month   name    flag
1       abc     TRUE
2       xyz     TRUE
3       abc     TRUE
4       xyz     TRUE
5       abc     FALSE
6       abc     FALSE

I want to calculate month-cumulative distinct count of 'name' filtered by last 'flag' value (TRUE). I.e. I want to have a result:

month   count
1       1
2       2
3       2
4       2
5       1
6       1

In months 5 and 6 'abc' should be excluded because the flag switched to 'FALSE' in month 5

Nov 18, 2020 in Power BI by anonymous
• 8,870 points
2,361 views

1 answer to this question.

0 votes
MyFilteredCumulativeMeasure =
COUNTROWS(
    FILTER(
        GENERATE(
            ALL( 'MyTable'[name] )
            ,CALCULATETABLE(
                SAMPLE(
                    1
                    ,SUMMARIZE(
                        'MyTable'
                        ,'MyTable'[month]
                        ,'MyTable'[flag]
                    )
                    ,'MyTable'[month]
                    ,DESC
                )
                ,FILTER(
                    ALL( 'MyTable'[month] )
                    ,'MyTable'[month] <= MAX( 'MyTable'[month] )
                )
            )
        )
        ,'MyTable'[flag]
    )
)

This works on your sample. Might need some tweaking for the real data. Also likely to slow down significantly as data size increases. I'll continue noodling on it, because this doesn't feel quite right, but it's a good naive implementation.

answered Nov 18, 2020 by anonymous
• 65,730 points

Related Questions In Power BI

0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 52,340 points
38,277 views
0 votes
1 answer
0 votes
1 answer

Can I use DAX to apply row-level security by matching a substring in the username to a column value?

Yes, you can implement row-level security (RLS) ...READ MORE

answered 1 day ago in Power BI by anonymous
• 36,180 points
16 views
0 votes
1 answer

DAX : Occurrences of "count"

Try this for creating a calculated column ...READ MORE

answered Nov 22, 2018 in Power BI by Upasana
• 8,620 points
6,371 views
0 votes
1 answer
0 votes
1 answer

How to get month name from month number in Power BI?

You can use: MonthName = FORMAT(DATE(1, [Num], 1), ...READ MORE

answered Sep 24, 2020 in Power BI by Alisha
6,690 views
0 votes
1 answer

How do I count rows in one table based on values in another table using DAX?

If the tables are related, this is ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,730 points
23,522 views
0 votes
1 answer

How to ignore a slicer for one measure, but apply it on another?

In order to ignore Slicer you need ...READ MORE

answered Oct 1, 2020 in Power BI by Gitika
• 65,730 points
7,666 views
0 votes
1 answer

Power BI (DAX): Distinct Count Filtered by Condition

You can go with this: DistinctCountActiveMonths = CALCULATE( ...READ MORE

answered Nov 18, 2020 in Power BI by anonymous
• 65,730 points
5,990 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP