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,910 points
837 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,850 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,350 points
34,165 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
3,757 views
0 votes
1 answer

Count occurrences in DAX

You can use DAX functions to do ...READ MORE

answered Mar 9, 2019 in Power BI by Avantika
• 1,520 points
21,224 views
0 votes
1 answer

How to calculate cumulative Total and % in DAX?

Hi, If your table is ready with percentage ...READ MORE

answered Mar 18, 2019 in Power BI by Cherukuri
• 33,010 points
15,195 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
1,796 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,850 points
12,201 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,850 points
3,318 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,850 points
1,956 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP