Need help with DAX

[Avivek]

Hello Everyone,

I need help with a dax, I have the requirement to create a Total procedure and its percentage, the dax i created was 

Total Procedures = CALCULATE(SUM('Procedure Fact'[C501B_QTY]),ALLEXCEPT('Tag Usage Fact',

'Tag Usage Fact'[TAG_ID]),ALLEXCEPT('Systems Dim','Systems Dim'[SYSTEM_NM])

And this is how its filtering my data.

The dax basically shows a tag is taken by a TAG OWNER, and what % of time he utilized and % of time someone else borrowed from and used(Order Owner).From the above eg., Anu has a tag which she used 77.7% time and Ajay used 22.2% time

Now the issues come when I am trying to select a column from another table.

All my details are from Tag table but when I bring columns from either set or system table then i am getting issue.

But this is how it works when i bring set name from set name. What is the change i need to do in my dax or is there something i am missing.

Can please someone help me with this please.

[v-rzhou-msft · Accepted solution]

Hi @Avivek ,

According to your screenshot, I think your issue will appear after you add [SET_NAME] from "Set Dim" table into visual. I think this should be caused by ALLEXCEPT()  in your data model.

How did you relate "Procedure Fact" and "Tag Usage Fact" table in your data model, by [Tag ID] columns?

I suggest you to try ALL() function and Filter() in your code.

My Sample:

Measure:

Total Procedures =
CALCULATE (
    SUM ( 'Procedure Fact'[C501B_QTY] ),
    FILTER (
        ALL ( 'Procedure Fact' ),
        'Procedure Fact'[TAG_ID] = MAX ( 'Tag Usage Fact'[TAG_ID] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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