Forum Discussion

fereshtehaghaei's avatar
fereshtehaghaei
Icon for Helper II rankHelper II
2 years ago
Solved

How to create a LOD in Power BI

Hello,

My comapany migration from Tableau to Power BI.
I have the following Fixed LOD in tableau, which creates a new field based on response ID and and IF OR expression (question ID) and then it returns the response that was provided from that unique response ID. My LOD in Tableau was as follow:

 

{ FIXED [Response Unique ID]:
MIN(IF
[Question ID]= 701998 OR
[Question ID]= 774063
THEN [Response] END)}

 

The calling this new calculated field Agency since the question is asking about the agency name.

In Tableau I am using this new Field/column (Agency) as a filter in my reports so I can drill down to that agency and look into that Agency's ratings based on other answered questions.

 

I would appreciate any help and guidance, Thank You!!

 

How can I create this in Power BI

 

Need Help
Show and Tell
Tips and Tricks

10 Replies

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    fereshtehaghaei 

    measure = 

    calculate (

    min ( 'YourTable'[Response] ) , 
    ALL('YourTable'[Response Unique ID]), 

    'YourTable'[Question ID] = 701998 || 'YourTable'[Question ID] = 774063

    )

     

    let me know if it works for you .

     

    If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button 👍🤠

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please check the forum search, there have been quite a few LOD related questions recently.  Power BI does not support the concept of dynamic buckets, you need to provide your own pre-made list.

  • fereshtehaghaei's avatar
    fereshtehaghaei
    Icon for Helper II rankHelper II
    2 years ago

    DanP Thanks for your time and solution, your formula does extract the data based on the condition we wrote; however, it's not allowing it to be used as a filter to drill down and see how an Agency was rated based on other satisfactory questions. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi fereshtehaghaei ,

    You can create a calculated column as below to get it and apply this new calculated column on the slicer to filter the data.

    Agency =
CALCULATE (
    MIN ( 'Table'[Response] ),
    FILTER (
        'Table',
        'Table'[Response Unique ID] = EARLIER ( 'Table'[Response Unique ID] )
            && 'Table'[Question ID] IN { 701998, 774063 }
    )
)

    In addition, please note that the measure can't be applied on the slicer.

    Calculated Columns and Measures in DAX

    Calculated Columns vs Measures

    Best Regards

    • fereshtehaghaei's avatar
      fereshtehaghaei
      Icon for Helper II rankHelper II
      2 years ago

      Anonymous Thank you for your great solution, it does work and it creates the new column; however, I need to do the same thing for Unit's of each Agency and when I try to apply the same type of solution, I get 'A Circular dependency' error. Since I am a newbie what exaclty does the measure can't be applied to slicer? Does this mean that Power BI doesn't allow a calculated column to be part of the Filter selection panel? or does it mean a calculated column doesn't allow us to drill down further more into data?

      I have Angency's name as part of student's selected response and I also have student's response selecting the unit those students attended in that Agency. For instance student attended St. Joseph hostpital and attended the Pediatric unit in that hostpital so I need to filter the agency and then unit and see how they rated that hospital as well as that unit? I was able to do this in Tableau but it seems like Power BI is making things a bit complicated. Any lights you can shine on my way or help please?