Distinctcount across 3 levels without countning X and blanks() in the measure

[Chris_23]

Hello guys,

I have a matrix with 3 levels. For the second level, a sum is calculated over the third and second levels if the second Level is "open". Otherwise, there is an 'X'. Now, I want to know for each row how many values from the second level are used to calculate sums, within the respective filter context. In the corresponding example, the value '5' should be assigned for each entry

Distinctcount w.o. X.pbix

Any idea how I can get this done? Thank you for your help!

Chris

[Anonymous · Accepted solution]

Thanks for the reply from Kedar_Pande.

Hi @Chris_23 ,

Please try the following dax:

CountNonXValues =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Master'[Name] ),
        FILTER (
            ALLSELECTED ( 'Master' ),
            'Master'[Sum] <> "X"
                && 'Master'[Sum] <> BLANK ()
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Master'[Region] ),
        IF (
            ISINSCOPE ( 'Master'[Region] ) && NOT ( ISINSCOPE ( 'Master'[Name] ) ),
            IF ( 'Master'[Sum] <> "X" && 'Master'[Sum] <> BLANK (), _count, BLANK () ),
            BLANK ()
        ),
        _count
    )

 This is the result:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Chris_23]

Thanks for your reply. This is absolutely the solution I've looked for.

[Chris_23]

No, unfortunately that doesn’t lead to the desired result. In the respective filter context, each row should show 5, as a sum is being calculated over 5 rows.

[Kedar_Pande]

Count Open Values = 
VAR OpenValuesCount = 
CALCULATE (
COUNTROWS ( 'YourTable' ),
'YourTable'[SecondLevel] = "Open" // Adjust based on your actual column name
)
RETURN 
IF ( OpenValuesCount > 0, OpenValuesCount, BLANK() )

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande