topic Re: Create a dimnesion based on a field to categorize other measures in DAX Commands and Tips

Create a dimnesion based on a field to categorize other measures

Manudata — Sun, 28 Jan 2024 20:20:39 GMT

Hello, I am trying to make a visualization and I am unable to find the way

I explain myself with a simple model, where I have a fact table with records of picking harvest jobs, a dimension table with the fruit plantation where the collection was donde and another table with the owners of this plantations.

The field I want to create is a size range field, so that I can make a visualization that tells me for a certain size range, when there are fields, how much has been collected in them.

At the same time, this range field should function as a filter, so that if I click on the range 100-200, in a table where I have the list of plantations, it will tell me which ones they are.

An additional complication is that if I filter for example idfruits, the measurements inside must be dynamic (example with no. owners: After filtering certain idfruits, one owner that I am counting in a range of 1000-2000 when removing certain plantations, I must count him in 100-500 range)

Re: Create a dimnesion based on a field to categorize other measures

Anonymous — Mon, 29 Jan 2024 01:30:44 GMT

Hi Manudata

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Create a dimnesion based on a field to categorize other measures

talespin — Mon, 29 Jan 2024 05:32:05 GMT

Hi Manudata ,

I think this is the solution you need. Create groups in Power BI or Power Query, there are multiple solutions shown here.

Create Buckets or Groups with Power Query in Power BI - YouTube

Re: Create a dimnesion based on a field to categorize other measures

Jihwan_Kim — Mon, 29 Jan 2024 06:00:26 GMT

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways is having the range_surface table like below.

Quantity total: = VAR _rangetablecondition = FILTER ( d_plantation, CALCULATE ( SUM ( d_plantation[surface_size] ) ) >= MIN ( d_rangesurface[min] ) && CALCULATE ( SUM ( d_plantation[surface_size] ) ) <= MAX ( d_rangesurface[max] ) ) RETURN CALCULATE ( SUM ( f_harvest[quantity] ), _rangetablecondition )

# plantation: = VAR _rangetablecondition = FILTER ( d_plantation, CALCULATE ( SUM ( d_plantation[surface_size] ) ) >= MIN ( d_rangesurface[min] ) && CALCULATE ( SUM ( d_plantation[surface_size] ) ) <= MAX ( d_rangesurface[max] ) ) RETURN CALCULATE ( COUNTROWS(DISTINCT(d_plantation[idplantation])), _rangetablecondition )

# manager: = VAR _rangetablecondition = FILTER ( d_plantation, CALCULATE ( SUM ( d_plantation[surface_size] ) ) >= MIN ( d_rangesurface[min] ) && CALCULATE ( SUM ( d_plantation[surface_size] ) ) <= MAX ( d_rangesurface[max] ) ) RETURN CALCULATE ( COUNTROWS(DISTINCT(d_plantation[idmanager]) ), _rangetablecondition )