Forum Discussion

Avivek's avatar
Avivek
Post Partisan
4 years ago
Solved

Need help with DAX

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.

 

 

 

 

 

 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.