cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hao
Frequent Visitor

Is there an equivalent concept of the "dimension calculated field" of QuickSight in PowerBI?

Hi, there

 

I am just moving from QuickSight to PowerBI. Everything looks good except for one concept. In QuickSight, there are two types of calculated fields:

  • One is the "calculated measure field", which could be used for the "value" of "Matrix" visual
  • The other is the "calculated dimension field", which could be used for "row" or "column" of the "Matrix" visual (it also means you can use a calculated field for grouping)

I believe "measure" in PowerBI equals the "calculated measure field" of QuickSight. But what is the equivalent "calculated dimension field" in PowerBI?

 

Hao

1 ACCEPTED SOLUTION

@Hao , Got we can have calculated column based on parameter. what you need do I create measure which gives that value

Size = IF(Max(Table1[a]) < Threshold[Parameter Value], "S", "L")

 

Have a table with values S and L , Say Bucket

 

You need row level id column in the table, if required add index column in the power bi

new Measure
Sumx(filter(Values(Table[Index]), [size] = max(bucket[Value])), [a])

 

 

or see if this can work

Sumx(filter(Table, [size] = max(bucket[Value])), [a])

 

 

refer

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Hao , I think field parameter for May 2022, update should fill that gap

 

https://powerbi.microsoft.com/en-us/blog/power-bi-may-2022-feature-summary/#post-19361-_Toc103022212



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Hao
Frequent Visitor

Hi @amitchandak,

 

Thanks for your quick reply! I look at this new feature. It improved but still has a gap with the "calculated dimension field". The key difference is:

 

- PowerBI field parameter requires either column (one time of one-to-one transformation) or measure (dynamic N to one aggregation)

- QuickSight "calculated dimension field" is a dynamic one-to-one transformation

 

In summary, QuickSight "calculated dimension field" is "PowerBI field parameter" + "dynamic one-to-one transformation".

 

I put an example here to help you understand my scenario:

 

Step1: I have a table "Table1" with a single numeric column "a":

Hao_0-1652948555650.png

Step 2: I have a what-if parameter "Threshold" to allow the user to choose a threshold.

Step 3: I want to add a measure "Size" to "Table1" like

 

Size = IF(Table1[a] < Threshold[Parameter Value], "S", "L")

 

Step 4: Finally, I am trying to use "Size" as a "field parameter" to form a Matrix visual like below

Hao_1-1652948895697.png

Unfortunately, I didn't figure out a way to do that because of step 3. "Size" is a measure that requires an "aggregation operator" (e.g., SUM, AVERAGE). QuickSight "calculated dimension field" has no such restriction.

 

Hao

 

 

 

 

 

 

@Hao , Got we can have calculated column based on parameter. what you need do I create measure which gives that value

Size = IF(Max(Table1[a]) < Threshold[Parameter Value], "S", "L")

 

Have a table with values S and L , Say Bucket

 

You need row level id column in the table, if required add index column in the power bi

new Measure
Sumx(filter(Values(Table[Index]), [size] = max(bucket[Value])), [a])

 

 

or see if this can work

Sumx(filter(Table, [size] = max(bucket[Value])), [a])

 

 

refer

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Hao
Frequent Visitor

@amitchandak, you pointed me in the right direction! Thank you so much!

 

My final formula looks like this:

COUNTX(FILTER(Table1, countrows(filter(Bucket, Table1[Size] == Bucket[Size])) > 0), [a])

But the complexity of the computation of my version is O(M*N), in which N is the size of "Table1", and M is the size of "Bucket". When M or N is huge, the refresh of the page is extremely slow.

 

Your version looks more optimized (much faster than mine):

countx(filter(Table1, [size] = max(Bucket[Size])), [a])

However, it doesn't return the correct result for me ("max" causes "S" to be used in comparison forever); what is your original intention of "max"? I hope to learn it for optimization.

Hao_0-1652959402680.png

 

BTW, I will mark your answer as a solution.

 

Hao

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors