Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

 

 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!

 

Data_Original.PNG

 

Regards,

Poonam

 

4 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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:

18.PNG

 

Regards,

Xiaoxin Sheng

View solution in original post

Anonymous
Not applicable

Hi Sheng.

 

Thank you so much for the reply 🙂

 

View solution in original post

Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

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")))

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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:

18.PNG

 

Regards,

Xiaoxin Sheng

Anonymous
Not applicable

 

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
Not applicable

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
Not applicable

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
Not applicable

Hi Sheng.

 

Thank you so much for the reply 🙂

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors