Create a dataset or one calculated column based on condition in Power BI

[Anonymous]

 Hello,

I am very new to Power BI. I am woring on one report. I have below data in my MSSql database. In 'Portfolio' column, I have 3 distinct values - PS, Care, Product.

For one of the chart in the report, I want to show only those records in which all 3 Portfolio is present. For the below scenarion,

I want to show one Pie chart in which I have to consider only Index1=1 values as all 3 values of product present only in index1=1, so it will be pie chart with values 10, 20 and 30.

I have written one query in Sql.

select index1, sum([rev(k)]) from t2
group by index1
having count(distinct portfolio)=3

In Power BI, I couldn't find any DAX formula to convert 'having' from sql in DAX.

so I converted the query to the below 'Where' clause:

select index1, po from (
select index1, count(distinct portfolio) as po from t2
group by index1) a
where po=3

I created the new measure in Power BI:

Test = (
FILTER (
    SUMMARIZE(
        VALUES ( 'T2'[Index1] ),
        "po", CALCULATE ( DISTINCTCOUNT(T2[Portfolio] ) )
    ),
    [po] =3
)
)

But it gives me only one value i.e. 3. I want all those Index1 so that I can calulate revenue for only those indexes. and show their ratio in Pie chart.

Can anyone please help me to know how to achieve it?

Thank you!

Regards,

Poonam

[Anonymous · Accepted solution]

Hi @Anonymous,

Maybe you can try to use below formula. (I create a variable to store matched index list, then use it as parameter to filter table)

Test = 
VAR indexList =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE ( T2, [Index], "Distict", DISTINCTCOUNT ( T2[PL] ) ),
            [Distict] >= 3
        ),
        "Index", [Index]
    )
RETURN
    SUMMARIZE (
        FILTER ( ALL ( T2 ), [Index1] IN indexList ),
        [Index1],
        "Total", SUM ( T2[Rev] )
    )

Result:

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi Sheng.

Thank you so much for the reply 🙂

[Anonymous · Accepted solution]

Hi @Anonymous,

Subquery should similar as above, you can add it in variable and link with or logic:

The IN operator in DAX

Notice: in operator not works for compare many to many.

Regards,

Xiaoxin Sheng

[Anonymous]

I @Anonymous,

Reference :

http://community.powerbi.com/t5/Desktop/Create-a-dataset-or-one-calculated-column-based-on-condition-in/m-p/427726#M196907

The formula which you mentioned is working as I want to show the ratio of only those records in which all 3 values are present. (Which we got in new table Test). Based on the image attached earlier, we need to show only Index1=1 records as all 3 values are there only for index1. But when I am trying to create chart based on that 'Test' table, I am using Dimention from other table (T2) table. At that time it is not working. I am using below formulaes:


Care Revenue(PL) = calculate(sum(Test[Total])*1000,Test[Total]>0,Test[PL]="Care")

Product Revenue(PL) = calculate(sum(Test[Total])*1000,Test[Total]>0,Test[PL]="Product")

Services Revenue(PL) = calculate(sum(Test[Total])*1000,Test[Total]>0,Test[PL]="Services")



I was not able to create link between Test table and 'T2' table.

Now I am trying in different way. I am pulling only Index1 in Test table and cretaing above measures in 'T2' table only. But I want to add one more condition, i.e. consider only those indexes which are present in 'Test' table.


i.e for example for 'Care measure':

Care Revenue(PL) = calculate(sum(Test[Total])*1000,Test[Total]>0,Test[PL]="Care") --> Additionally one more filter: index1 in (Test table index1)

It's like subquesry in sql.

select sum([rev(k)]) from t2 where index1 in (
select index1 from t2
WHERE [Material flag] =1
group by index1
having count(distinct portfolio)=3)
and portfolio='Care'


Is there any way to achieve that? Thank you!


Regards,

Poonam

[Anonymous]

Hi @Anonymous,

Measure not works for your scenario, measure is impossible to return table, please use calculated table to instead.

Table formula:

Test =
SUMMARIZE (
    FILTER ( ALL ( T2 ), DISTINCTCOUNT ( T2[Portfolio] ) = 3 ),
    [Index1],
    "Total", SUM ( T2[Rev] )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

Thank you so much for your reply.

When I tried the below calculation, it is not returning any record in the table.

Test =
SUMMARIZE (
    FILTER ( ALL ( T2 ), DISTINCTCOUNT ( T2[Portfolio] ) = 3 ),
    [Index1],
    "Total", SUM ( T2[Rev] )
)

I tried some different calculation:

Table_Portfolio = FILTER (
    SUMMARIZE (
        'T2',
        'T2'[Index1],
        "po",  CALCULATE ( DISTINCTCOUNT(T2[Portfolio] ))
    ),
    [po] =3
)

It returned only those indexes which I want. But I want Portfolio and Rev column in the table. If I changed my calculation to below to get other 2 columns, It doesn't return anything again. Could you please help me on that.

Table_Portfolio = FILTER (
    SUMMARIZE (
        'T2',
        'T2'[Index1], T2[Portfolio], T2[Rev(k)],
        "po",  CALCULATE ( DISTINCTCOUNT(T2[Portfolio] ))
    ),
    [po] =3
)

Thank you!

Regards,

Poonam

[Anonymous · Accepted solution]

Hi @Anonymous,

Maybe you can try to use below formula. (I create a variable to store matched index list, then use it as parameter to filter table)

Test = 
VAR indexList =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE ( T2, [Index], "Distict", DISTINCTCOUNT ( T2[PL] ) ),
            [Distict] >= 3
        ),
        "Index", [Index]
    )
RETURN
    SUMMARIZE (
        FILTER ( ALL ( T2 ), [Index1] IN indexList ),
        [Index1],
        "Total", SUM ( T2[Rev] )
    )

Result:

Regards,

Xiaoxin Sheng

[Anonymous]

The formula which you mentioned is working as I want to show the ratio of only those records in which all 3 values are present. (Which we got in new table Test). Based on the image attached earlier, we need to show only Index1=1 records as all 3 values are there only for index1. But when I am trying to create chart based on that 'Test' table, I am using Dimention from other table (T2) table. At that time it is not working. I am using below formulaes:


Care Revenue(PL) = calculate(sum(Test[Total])*1000,Test[Total]>0,Test[PL]="Care")

Product Revenue(PL) = calculate(sum(Test[Total])*1000,Test[Total]>0,Test[PL]="Product")

Services Revenue(PL) = calculate(sum(Test[Total])*1000,Test[Total]>0,Test[PL]="Services")



I was not able to create link between Test table and 'T2' table.

Now I am trying in different way. I am pulling only Index1 in Test table and cretaing above measures in 'T2' table only. But I want to add one more condition, i.e. consider only those indexes which are present in 'Test' table.


i.e for example for 'Care measure':

Care Revenue(PL) = calculate(sum(Test[Total])*1000,Test[Total]>0,Test[PL]="Care") --> Additionally one more filter: index1 in (Test table index1)

It's like subquesry in sql.

select sum([rev(k)]) from t2 where index1 in (
select index1 from t2
WHERE [Material flag] =1
group by index1
having count(distinct portfolio)=3)
and portfolio='Care'


Is there any way to achieve that? Thank you!

Created a new post:

http://community.powerbi.com/t5/Desktop/Subquery-in-Power-BI/m-p/436382#M201243

Regards,

Poonam

[Anonymous · Accepted solution]

Hi @Anonymous,

Subquery should similar as above, you can add it in variable and link with or logic:

The IN operator in DAX

Notice: in operator not works for compare many to many.

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @Anonymous,

It is working as expected. I used below formula to calculated 'Care Revenue' by considering below indexes.

Thank you so much!! 🙂

Care Revenue (PL) =
VAR indexList =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE ( T2, [Index1], "Distict", DISTINCTCOUNT ( T2[Portfolio] ) ),
            [Distict] = 3
        ),
        "Index", [Index1]
    )
RETURN
calculate(sum(T2[Rev(k)]), filter(T2,AND(T2[Index1] in indexList , T2[Portfolio]="Care")))

[Anonymous · Accepted solution]

Hi Sheng.

Thank you so much for the reply 🙂