Combining calc. measure with Category dimension

[skitzo]

Hi,

I have 2 tables. DimCategory and FactGL. I build calculated measure Days for which I would like to use DimCategory table to analyze in which category it belongs to. Basically, the Days calc measure should be connected with DimCategory table in a way that it falls between From and To.

Any clues how to solve this?

Here's the mockup of the model in Power BI:

 

[v-shex-msft]

Hi @skitzo,

You can refer to below formula if it suitable for your requirement:

Calculate column:

Category = CALCULATE(VALUES(Category[Category]),FILTER(ALL(Category),[From]<=EARLIER(Records[Days])&&[To]>=EARLIER(Records[Days])))

Notice: first table(Category), second table(Records)

If above not help, please share some detail contents.

Regards,

Xiaoxin Sheng

[skitzo]

Hi @v-shex-msft,

this is the error that i'm getting:

 

This measure(Broj dana u dugovanju) is actually a calculated measure so this might be the problem here? It's not persisted in a table.

Btw, measure and column names are maybe little changed in the screenshot but idea remains the same.

[v-shex-msft]

Hi @skitzo,

You can try to remove EARLIER function and test.(earlier works on calculated column)

Category = CALCULATE(VALUES(Category[Category]),FILTER(ALL(Category),[From]<=[Measure]&&[To]>=[Measure]))

Regards,

Xiaoxin Sheng

[skitzo]

Hi,

@v-shex-msft

Here's the error i have now:

Any ideas how to solve this?

[ceebu]

Are you using the DIfference between the dates in the subsequent rows as the formula for calculating days ?

(Day 1 to Day 6 is 5 days and Day 6 to Day 10 is 3 days)  - Maybe that is why you are getting a circular reference error msg ? What is the value in Salda Konti [Column] ?

If your "category" definitions are not too huge, I would recommend adding them as another "calculated" column in the original table.

See here for more info on circular context errors: https://www.sqlbi.com/articles/understanding-circular-dependencies/

[skitzo]

Hi @ceebu,

here's the formula in the calculated measure:

Should i do this formula differently so the circular reference is avoided?

My category definitions hew few rows but big ranges. Also I cannot define this in original table since the category depends on the measure [Broj dana u dugovanju] which is changing with the different date selection in a slicer 😕

The analysis which I'm trying to build is AR Open items.

[v-shex-msft]

Hi @skitzo,

Can you please provide some sample data to test?

Regards,

Xiaoxin Sheng

[skitzo]

Hi @v-shex-msft,

I sent you the date in the private message? HAve you received it?

[v-shex-msft]

Hi @skitzo,

I got a 404 error when I try to open the link, can you ensure you have correct setting the share file?

Regards,

Xiaoxin Sheng

[skitzo]

Hi @v-shex-msft, 

I posted a new link to your inbox

[v-shex-msft]

Hi @skitzo,

Based on test, it seems works on my side.

BTW, below is the the sample file(updated). 

https://1drv.ms/u/s!AoIROK69-Gf7gnpnqaar_hoSsYEm

Regards,
Xiaoxin Sheng

[skitzo]

Hi @v-shex-msft,

Yes you are right, it works when you build is as a measure. What if you want to use dimension Klase for analysis instead of the Category measure that you built? The idea is that i want to use Klase dimension because i can say "Show empty values" so that  entire range is rendered in table instead of just values that are present in current Category measure.

In this current scenario, you cannot pivot "Category" because it's a measure but I would like to have this Category pivoted. Here's how I would like to have it as final product:

[v-shex-msft]

Hi @skitzo,

If you can convert 'Broj dana u dugovanju' measure to calculate column and keep the correct value, it may possible to add a custom catagory, but I failed on my side.

Regards,

Xiaoxin Sheng

[skitzo]

OK, thank you for your help! Will try to build it up from here..