Unable to establish relationships to filter dimensions

[Borja204]

Hi all,

First of all thanks for your help. I am not an expert in power bi and I am struggling a problem that I am unable to solve.

The main issue is that I do have many relations inactive due to ambiguity. After reading trhough the forums and googling it, it seems that this is called slowly changing dimensions (I am not completely sure that this is my case...)

This is the model (indeed its only a part of the model but the one with the problem):

ReportsData is the fact table. ReportCenters, ReportsCountries, ReportsProjects etc are the dimension tables. The main issue is that those dimensions change every month. So, in each one of those dimensions tables, I have a LoadReference property that holds the year-month  reference to which it belongs.

For example, for the projects table, I may have

ProjectId	LoadReference	Name
1	202009	Project1
1	202010	Project1Modified
2	202010	Project2
2	202009	Project2

I'd need to be able to filter the dimensions at report level (or something like this), because the user must be able to select the year-month of the dimensions. So, if the consumer of the report selects 202010, in the project dimensions table it should appear Project1Modified  and Project2.  This two names is what it should appear later inthe slicers for future filtering.

If someone could give me some starting point or help to accomplish I will be eternally grateful.

Thanks and regards,

[amitchandak · Accepted solution]

@Borja204 , You can not get all active. You have to change the design. Or make all of them inactive and activate then using userealtion in a measure

[Borja204]

Hi @vivran22 ,

Here we go, I created a dummy file with dummy data (and only left 3 dimension tables to made it simpler). Is the same thing that I explained above. The main issue is that I need to filter the dimensions based on the LoadReferences table (which I have set in a global filter).

PBIX Data 

@amitchandak Thanks for your response, could you point me in the right direction about changing the design to accomplish what I need? I cant change the origin model.

Thanks all in advance,

Regards

[vivran22 · Accepted solution]

@Borja204 

Thank you for the same file.

Here I have made a few changes:

Relationship:

The objective is that using LoadReferences table, we will filter the dimension tables and then subsequently we will filter the data table.

Then, I have used the following measure for calculation

Sum Value (Concept) =
CALCULATE (
    SUM ( Data[Value] ),
    USERELATIONSHIP ( Concepts[ConceptCode], Data[ConceptCode] )
)

Following is the output:

Attaching the file as well (below signature)

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Borja204]

Hi @vivran22 ,

I understood what you did. Thanks! 

I have couple of doubts now. Before, I used the value field of the 'Data' table to make the agreggates. In the matrix, I could easily configure for example: 

See sum of value per country then per concept with Years in the columns (countryName and conceptyType in row groups, year in column and value in sum of values) But now, the measure I saw you did, is only for sum of value per concepts.

How can I mix all these things together?

Should I make a sum of value measure per each table?

If so, which one should I choose when selecting more than one dimension in row or columns groups like the example I mentioned above?

Again, big thanks, I am new to this and I had never had such a "complex" model so maybe obvious things are not so ovbious for me.

Regards,

Borja

[vivran22]

@Borja204 

You are correct. You need to create measures for all such calculation. How can you display this is up to you. You may create different visual dedicated to each calculation, or alternatively, you may use calculation groups to bring everything on same page. You may find following post useful:

Solved: Re: Combine metrics from different dataset/source/... - Microsoft Power BI Community

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Borja204]

Hi again @vivran22 ,

I have been trying all this morning to achieve multiple userelationship or using calculation group but I am missing still something (even though I tough I was not).

Could you send me the same pbix but showing in the matrix this visual?

Columns: conceptGroup

Rows: countryName, then projectName

Value: Sum of value

Sorry for the invonveniences and thanks for your time,

Regards,

[Borja204]

Hi @vivran22 ,

It holds some confidential data so give me sometime and I am preparing an example with dummy data and some tables.

I'll post it here when I have it ready,

Thanks again,

Regards

[Borja204]

Hi @vivran22 @amitchandak ,

Thansk for your responses but I still dont get what I am suposed to do. The loadReferences table shown in the image, is indeed, unique. It holds only 1 yearMonth per row. But still I cant get all the relations active, only one of them.

Thanks in advance and regards,

[amitchandak · Accepted solution]

@Borja204 , You can not get all active. You have to change the design. Or make all of them inactive and activate then using userealtion in a measure

[vivran22]

@Borja204 

Can you share the sample pbix file?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[vivran22]

Hey @Borja204 ,

You can add a table for unique Load Reference property and then link it with your dimension and fact tables. This unique Load Reference table then can act as a filter table to filter your data tables (both fact and dimensions).

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[amitchandak]

@Borja204 ,Based on what i got.

You have merge load reference table with all the table above it. or craete that many copies and join with each table