Forum Discussion

shubh_kush's avatar
shubh_kush
Icon for Helper I rankHelper I
5 years ago
Solved

Generic Column based on other columns in Powerbi

Hi Everyone,
 
I have one query in my Powerbi report. Below are the three different benefits (Benefit 1, Benefit 2 & Benefit 3) with another column Deployment Model in a table visual. (Ref: Table visual 1)
Table Visual 1:
                                            
 
I want to add another table visual in the same report, which will show all the Benefits in a single column (Generic - Benefit 1 + 2+ 3) and counts with respect to each Deployment Model. (Ref: Table Visual 2). 
Table Visual 2:
                                         
 
I'm not able to create the Generic Benefit column to have all the Beneifts. I tried creating a separate table and joined them together but I can only use one Benefit column to establish the relationship.
 
Note: There are other benefits as well (like Digital readiness, etc) which will come later in the data but should in the Generic column in Table Visual 2.
 

Thanks in Advance

 

 

 

  

                                    

 

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    Hi shubh_kush ,

     

    You can create a seperate table for unique benefits as below

     

    Table 2 =
VAR distnct_benefit1 =
    DISTINCT ( Deployment[Benefit 1] )
VAR distnct_benefit2 =
    DISTINCT ( Deployment[Benefit 2] )
VAR distnct_benefit3 =
    DISTINCT ( Deployment[Benefit 3] )
RETURN
    DISTINCT ( UNION ( distnct_benefit1, distnct_benefit2, distnct_benefit3 ) )

     

     

    Now create a two measure one for onprem and another for cloud with below code

     

     

    Cloud =
VAR result =
    CALCULATE (
        COUNT ( Deployment[Deployement] ),
        FILTER (
            Deployment,
            Deployment[Deployement] = "Cloud"
                && (
                    Deployment[Benefit 1] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 2] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 3] = MAX ( 'Table 2'[Benefits] )
                )
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

     

     

     

    On_Prem =
VAR result =
    CALCULATE (
        COUNT ( Deployment[Deployement] ),
        FILTER (
            Deployment,
            Deployment[Deployement] = "On Prem"
                && (
                    Deployment[Benefit 1] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 2] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 3] = MAX ( 'Table 2'[Benefits] )
                )
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

     

      

    Now add benefits column from newly created table and add above measure you will see required output as below

     

     

    Thanks,

    Samarth

5 Replies

    • shubh_kush's avatar
      shubh_kush
      Icon for Helper I rankHelper I
      5 years ago

      I tried Unpivoting it but it will change the structure of the table and data. I'm looking for a solution by which I can create one single column with all the Benefits value, so that I can show count based on each Deployment type. The issue I'm facing with the 3 different benefits columns as I cannot create 3 differnet relationship between two tables.

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi shubh_kush ,

     

    You can create a seperate table for unique benefits as below

     

    Table 2 =
VAR distnct_benefit1 =
    DISTINCT ( Deployment[Benefit 1] )
VAR distnct_benefit2 =
    DISTINCT ( Deployment[Benefit 2] )
VAR distnct_benefit3 =
    DISTINCT ( Deployment[Benefit 3] )
RETURN
    DISTINCT ( UNION ( distnct_benefit1, distnct_benefit2, distnct_benefit3 ) )

     

     

    Now create a two measure one for onprem and another for cloud with below code

     

     

    Cloud =
VAR result =
    CALCULATE (
        COUNT ( Deployment[Deployement] ),
        FILTER (
            Deployment,
            Deployment[Deployement] = "Cloud"
                && (
                    Deployment[Benefit 1] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 2] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 3] = MAX ( 'Table 2'[Benefits] )
                )
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

     

     

     

    On_Prem =
VAR result =
    CALCULATE (
        COUNT ( Deployment[Deployement] ),
        FILTER (
            Deployment,
            Deployment[Deployement] = "On Prem"
                && (
                    Deployment[Benefit 1] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 2] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 3] = MAX ( 'Table 2'[Benefits] )
                )
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

     

      

    Now add benefits column from newly created table and add above measure you will see required output as below

     

     

    Thanks,

    Samarth

    • shubh_kush's avatar
      shubh_kush
      Icon for Helper I rankHelper I
      5 years ago

      Your solution works perfectly fine 🙂

      Thanks a lot. But the total for the column is not correct. Instead of total, it is showing Maximum.

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        5 years ago

        For getting correct total you can create another two measure apart from above mentioned measures.

        cloud_total =
SUMX (
    SUMMARIZE ( 'Table 2', 'Table 2'[Benefits], "_cloud_sum", [Cloud] ),
    [_cloud_sum]
)

         

        onprem_total =
SUMX (
    SUMMARIZE ( 'Table 2', 'Table 2'[Benefits], "_onprem_sum", [On_Prem] ),
    [_onprem_sum]
)

         

        Update count measure as below:-

        Count = [cloud_total] + [onprem_total]

         

        Use these measures in table visual:-

        Note:- Dont delete previously provided measure since we have used those measure only to get correct total.

         

        Thanks,

        Samarth