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 ...
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

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