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
1,788 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,910 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,360 points
36,933 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
5,214 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
25,635 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,030 points
18,272 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
5,529 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,910 points
21,639 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,910 points
6,364 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,910 points
4,707 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