Forum Discussion

Kostas's avatar
Kostas
Icon for Helper IV rankHelper IV
6 years ago
Solved

Measure to show value based in a list with multiple variables

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

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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's avatar
    v-alq-msft
    6 years ago

    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

7 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Kostas
      Icon for Helper IV rankHelper IV
      6 years ago

      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's avatar
        Pragati11
        Icon for Super User rankSuper User
        6 years ago

        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

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Kostas
      Icon for Helper IV rankHelper IV
      6 years ago

      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's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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