Forum Discussion

Viktor001's avatar
Viktor001
Frequent Visitor
5 years ago
Solved

Power BI | Slicer Filter | Summary Table generation

I have a requirement in which I have transformed data in a Table which has say 10 rows. Another table has same columns but has some different data say another 10 columns. Both these tables have a row "total" which displays aggregated data at the end. I want to build another table in the report which gives the total from both these tables. But I am not able to link these tables with any relationship. So slicer filter visualization applies the filter on individual tables but does not reflect changes on summary table. Can you suggest how to acheive filtering throughout the report in this scenario? Or any other approach would also be appreciated. FYI I am preprocessing data from raw JSON. Below given is an example for the same. 

 

Table 1:-

IDTypeValue1Value2
1A1015
2A1015
3A1015
  3045

 

Table 2:-

IDTypeValue1Value2
1B2520
2B2520
3B2520
  7560

 

Summary:-

 ABTotal
Value13075105
Value24560105

 

Column "ID" is used to apply filter using slicer visualization. In this case when we change ID in slicer filter, table 1 and 2 reflects changes but summary table doesn't as they don't have any relation. How to link them in power BI or any other approach to acheive the same?

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    Viktor001 

    You need to create dimension tables that hold the unique values for ID and Type that you link to your fact tables, then you can use measures to summarize your data.  You can use DAX to create the tables:

     

    IDs = 
DISTINCT(
        UNION(
            DISTINCT('Table 1'[ID]),
            DISTINCT('Table 2'[ID])
    )
)
    Types = 
DISTINCT(
        UNION(
            DISTINCT('Table 1'[Type]),
            DISTINCT('Table 2'[Type])
    )
)

     

    Then create the relationships like this.

    Then some measure to do the sums and add the type (from your types table) to a visual.  In my sample I put the measures on the IDs table but you can put them on whatever table you want.

    I have attached my sample file for you to look at.

     

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    Viktor001 

    You need to create dimension tables that hold the unique values for ID and Type that you link to your fact tables, then you can use measures to summarize your data.  You can use DAX to create the tables:

     

    IDs = 
DISTINCT(
        UNION(
            DISTINCT('Table 1'[ID]),
            DISTINCT('Table 2'[ID])
    )
)
    Types = 
DISTINCT(
        UNION(
            DISTINCT('Table 1'[Type]),
            DISTINCT('Table 2'[Type])
    )
)

     

    Then create the relationships like this.

    Then some measure to do the sums and add the type (from your types table) to a visual.  In my sample I put the measures on the IDs table but you can put them on whatever table you want.

    I have attached my sample file for you to look at.

     

    • Viktor001's avatar
      Viktor001
      Frequent Visitor
      5 years ago

      Thanks a lot for the solution. It works fine. But can you suggest how to do the same if we only have 1 table by merging Table 1 and Table 2. And we want to achieve the same goal of summarizing Data based on Type.