Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.