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 r...
Need Help
Show and Tell
Tips and Tricks
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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi fereshtehaghaei ,

    Please update the formula of calculated column [New-Unit] and check if it can return the expected result...

    New-Unit =
CALCULATE (
    MIN ( vm_rawDataQuestionsOptionsCategories[optionText] ),
    FILTER (
        vm_rawDataQuestionsOptionsCategories,
        vm_rawDataQuestionsOptionsCategories[responseUniqueID]
            = EARLIER ( vm_rawDataQuestionsOptionsCategories[responseUniqueID] )
            && vm_rawDataQuestionsOptionsCategories[questionID]
            IN { 740044, 740045, 740076, 740047 }
    )
)

    For circular dependencies error, you can review the following blogs to get more details.

    Understanding circular dependencies in DAX - SQLBI

    Avoiding circular dependency errors in DAX - SQLBI

    Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community

    Best Regards

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

      Thank You for looking into this and the new update. I did try that table name that you mentioned but I still got the "Circular Dependecy" error. I will review the blogs you have had shared. Thank You

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

    a circular dependency means that two column definitions depend on each other. Say

     

    Column 1 = [Column 2] - 1

    and

    Column 2 = [Column 1] + 1 

     

    That may work in Excel or Tableau, it will not work in Power BI.