How to create a LOD in Power BI

[fereshtehaghaei]

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

 

[v-yiruan-msft]

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]

@v-yiruan-msft 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?

[v-yiruan-msft · Accepted solution]

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]

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]

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.

[fereshtehaghaei]

@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. 

[lbendlin]

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]

@lbendlin I did look at the Forum before posting and none of the questions or solution were helping to solve my question. And still having no solution even after posting my own questions. Thanks

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Daniel29195]

@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 👍🤠