How to get blank count for only clusters in txn table

[rohank11]

I need to count title where brand and therapy is blank

I got cluster slicer from below table

It is showing same blanks for all clusters, it should show blank count only for clusters present in txn table and zero for the rest

Below measure is using

Total targets12 = CALCULATE(COUNT(Txn_Scorecard_Target[TargetName]),

          Txn_Scorecard_Target[Brand] = BLANK(),ALLEXCEPT(Txn_Scorecard_Target,Txn_Scorecard_Target[Brand],Txn_Scorecard_Target[Therapy])

)

model for reference

KPI in prod so urgent help will be appreciated

[rohank11 · Accepted solution]

Hi @v-sgandrathi ,
As above solutions were correct, none of them were complete solution
I found it using myself using below dax

Total targets12 = CALCULATE(

                DISTINCTCOUNT(Txn_Scorecard_Target[Index2]),

                FILTER(Txn_Scorecard_Target,Txn_Scorecard_Target[Brand] <> BLANK())) +

                CALCULATE(      

                DISTINCTCOUNT(Txn_Scorecard_Target[Index2]),Txn_Scorecard_Target[Brand] = BLANK(),

                REMOVEFILTERS(Mstr_Brand[Title]),

                REMOVEFILTERS(Mstr_Therapy[Therapy])

) +0

[v-sgandrathi]

Hi @rohank11,

I'm glad you found a solution and resloved  the query. Thank you very much for sharing here.

Kindly mark your reply as the accepted solution so that others in the community can find it quickly.

Thankyou for connecting with Microsoft Community Forum

[v-sgandrathi]

HI @rohank11,

I wanted to check if you had the opportunity to review the information provided by @maruthisp, @Royel, and @Bmejia. Thank you everyone for your response to the query.

Please feel free to contact us if you have any further questions.
Thank you and continue using Microsoft Fabric Community Forum.

[v-sgandrathi]

Hi @rohank11,

I wanted to check in your situation regarding the issue. Have you resolved it? Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @rohank11,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

[maruthisp]

Hi @rohank11 

Please find the below attached pbix file with a solution I came up.

get blank count for only clusters in txn table.pbix

Please let me know if you have any further questions or need clarifications.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[Royel]

Hi, @rohank11  you want to count the number of blanks when Brand and Therapy has no values. 

Try this measure: Now, this measure will count when both (Brand and Therapy) are blank. If, you want to improve, like if any one is blank then count use replace && with OR

Blank_Title_Count = 
CALCULATE(
    COUNTROWS('Transaction_Table'),
    FILTER(
        'Transaction_Table',
        ISBLANK('Transaction_Table'[Brand]) && 
        ISBLANK('Transaction_Table'[Therapy])
    )
)

 Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[Bmejia]

Willl this work

Total targets12 =

CALCULATE(

    COUNT(Txn_Scorecard_Target[Cluster]),

        Txn_Scorecard_Target[Brand]=""

            && Txn_Scorecard_Target[Therapy]="")