Help with dax please

DAX
1

Hey All,
looking for some support here with dax.

I have this single table PBI with sample data attached. and here is the scenario:
The idea is that Any combination of 2 slicers can be used in the report, the final output needed is count of ID’s that have an aggregated value ( based on applied filters) > = 1000

I have been able to get the numbers in the table, to identify the id’s that are >1000, and seem to work alright in the table.

But the issue remains in the card visual - that’s what should eventually tell the count of ID’s with revenue> 1000, currently, it doesn’t change when filters are applied.

image
image1371×600 31.3 KB

my current measure is :

ID’s with revenue >1000 =

VAR _v1 =CALCULATE(SUM(TestTable[Revenue]), REMOVEFILTERS(‘TestTable’[Sub.PricingItemName] ))

VAR _v2 = CALCULATE(COUNTROWS(VALUES(‘TestTable’[Accountid])), FILTER(TestTable, _v1 >=1000))

VAR _v3 = CALCULATE(_v2, ALLEXCEPT(TestTable, TestTable[Sub.Platform],TestTable[Accountid]))

Return

_v3

would appreciate your guidance with this.
Sample report1.pbix (31.7 KB)

2

Hello @jsodhi,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned. Below is the DAX measure alongwith the screenshot of the final results provided -

IDs w/Revenue Threhold - Harsh = ----- 'Count of ID's with Revenue More Than or Equal To 1000'
VAR _Count_of_IDs_wrt_Revenue_More_Than_or_Equal_To_1000 = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                TestTable , 
                TestTable[Accountid] , 
                TestTable[Sub.PricingItemName] ,
                TestTable[Sub.Platform] ,
                TestTable[Sub.OfferName] ,
                TestTable[Revenue] ) , 
            "@Revenue" , 
            CALCULATE( SUM( TestTable[Revenue] ) ,
                ALLEXCEPT( TestTable , TestTable[Accountid] , TestTable[Sub.Platform] , TestTable[Sub.OfferName] ) ) ) , 
        [@Revenue] >= 1000 ) )

VAR _Results = 
IF( NOT ISBLANK( _Count_of_IDs_wrt_Revenue_More_Than_or_Equal_To_1000 ) , 
    _Count_of_IDs_wrt_Revenue_More_Than_or_Equal_To_1000 , 
    0 )

RETURN
_Results

Final Results - 1
Final Results - 11542×617 29.5 KB

Final Results - 2
Final Results - 21570×665 27.3 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Count of IDs w.r.t Revenue more than or equal to 1000 - Harsh.pbix (34.6 KB)

2 Likes
3

Hey @Harsh ,

Thank you for your response. The solution you have shared gave correct result only in one of the possible scenarios. ( combination of filter being used) - thats probably because i didnt explain the scenarios very well in my previous post

I had also been working on the solution and looks like i was taking the same approach as you did. but was slightly off . and looking at your solution helped me fix mine.

[The version2 CountRevenue >1000] gives me the result that i was looking for - the measure also has my old code commented out

image
image1442×747 48.1 KB

here is the working pbx
Sample report2nd attempt.pbix (35.0 KB)

Although I think like problem is resolved, would be great if you can have a look at DAX and share any feedback incase you think it can be improvised

Either ways - I really appreciate your support. Thanks,.