Measure to show value based in a list with multiple variables

[Kostas]

Hello everyone, 

I am currently designing a dashboard with Power BI and I will need some help to overcome an issue that I have. 

I have created dummy data to explain the issue, I am basically need a way of moving from the first table into the second either by creating the second table from the data of the first by using the Power BI with DAX or Mquery or to receive the data that I need with a measure. The problem is that I do not know how to do either. 

My end goal is to have a single select filter that the user will select the theme that he/she wants to see results for (i.e. Dragon) and have a pie chart that will show the distict number of ID's and wheather they are complete or not complete.

The logic for complete or not is: If an answer and a conclusion exist then the ID is considered complete. If either the answer or the conclusion does not exist then the ID is considered incomplete.

Let me know if you need further information.

Thanks in advance

[v-alq-msft · Accepted solution]

Hi, @Kostas 

Based on your description, you may create two measures and a column as below.

Calculated column:
IsCompleted Column = 
var _theme = 'Table'[Theme]
var _id = 'Table'[ID]
return

    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Theme] = _theme&&
                'Table'[ID] = _id
            )
        )
        =
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Theme] = _theme&&
                'Table'[ID] = _id&&
                'Table'[Answer] = "Exist"&&
                'Table'[Conclusion] = "Exist"
            )
        ),
        "complete",
        "not complete"
    )

Measure:
IsCompleted Measure = 
var _theme = SELECTEDVALUE('Table'[Theme])
var _id = SELECTEDVALUE('Table'[ID])
return
IF(
    ISFILTERED('Table'[Theme])&&ISFILTERED('Table'[ID]),
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Theme] = _theme&&
                'Table'[ID] = _id
            )
        )
        =
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Theme] = _theme&&
                'Table'[ID] = _id&&
                'Table'[Answer] = "Exist"&&
                'Table'[Conclusion] = "Exist"
            )
        ),
        "complete",
        "not complete"
    )
)

Count = 
DISTINCTCOUNT('Table'[ID])

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft · Accepted solution]

Hi, @Kostas 

Based on your description, you may create two measures and a column as below.

Calculated column:
IsCompleted Column = 
var _theme = 'Table'[Theme]
var _id = 'Table'[ID]
return

    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Theme] = _theme&&
                'Table'[ID] = _id
            )
        )
        =
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Theme] = _theme&&
                'Table'[ID] = _id&&
                'Table'[Answer] = "Exist"&&
                'Table'[Conclusion] = "Exist"
            )
        ),
        "complete",
        "not complete"
    )

Measure:
IsCompleted Measure = 
var _theme = SELECTEDVALUE('Table'[Theme])
var _id = SELECTEDVALUE('Table'[ID])
return
IF(
    ISFILTERED('Table'[Theme])&&ISFILTERED('Table'[ID]),
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Theme] = _theme&&
                'Table'[ID] = _id
            )
        )
        =
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Theme] = _theme&&
                'Table'[ID] = _id&&
                'Table'[Answer] = "Exist"&&
                'Table'[Conclusion] = "Exist"
            )
        ),
        "complete",
        "not complete"
    )
)

Count = 
DISTINCTCOUNT('Table'[ID])

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kostas]

Hello,

Thank you for the response. 

I can see how you are making it work. 

Just a question, how the IsCompleted Column is interacting with the IsCompleted Measure?

Thanks again for showing me the logic.

Kind Regards

[v-alq-msft · Accepted solution]

Hi, @Kostas 

Actually, IsCompleted Column is the same logic as the IsCompleted Measure. The measure is calculated in the specific context while the column is in the data model. You need to use the IsCompleted as a legend in the pie chart, so you have to use the column. While the measure is calculated in the pivot table for clear display.

Best Regards

Allan

[Pragati11]

Hi @Kostas ,

Just create a column in your first table as follows:

CompletnessCheck = IF(Table1[Answer1] = "Exist" && Table1[Conclusion] = "Exist", "Complete", "Not Complete")

You end up with the above additional column in your 1st table. You can use it however you want to on your dashboard.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Kostas]

hello @Pragati11  and thanks for your comment,

Unfortunatelly I cannot use the method that you are proposing as that was my first thought also because it will duplicate the value

For example Theme = Dragon and ID=1 

The row by using your code will produce 2 different outcomes:

1) Complete (when the values in both answer and conclusion column is "Exist")

2) Not Complete (when in either of the two columns answer and conclusion is "Not Exist")

As a result when I will create the pie by placing as a category the Completeness Test and distinct count the ID column the ID =1 will be counted twice, once for the category "Complete" and once for the category "Not Complete".

I hope that makes sense

Kind Regards

[Pragati11]

Hi @Kostas ,

I understand the issue here but in that case you need to define your new column at some more granularity, not just at ID level.

Your current logic is:

The logic for complete or not is: If an answer and a conclusion exist then the ID is considered complete. If either the answer or the conclusion does not exist then the ID is considered incomplete.

Thanks,

Pragati

[Kostas]

Hello,

If I create the column you mentioned before (let's name it "TEST1") I will have two values either Complete or Not Complete

An example of my logic behind what I need to do is:

Theme = Dragon

ID = 1 

If any of the rows of theme = "Dragon" and ID = 1 of the column "TEST1" having the value  "Not Complete" then the whole ID = 1 for the specific theme is considered "Not Complete"

If all the rows that contain for theme "Dragon" and ID "1" having the value "Complete" in the TEST1 column then the whole ID is considered "Complete".

Thanks