Forum Discussion

Aikeno's avatar
Aikeno
Icon for Helper I rankHelper I
2 years ago

Measure based on several conditions

Hello everyone,

 

I would like to have a measure that would give me the result in the column "Test done" in blue table in my report.

Yellow and orange are data table and they are linked by the Project_ID.

I would like to measure for all my project:

If all and only activities VDV in "activity category" are closed in "activity state"

If yes I want to return "Yes" and if at least one of them is not closed I want to return "No" and if there is no VDV activities return "No VDV"

 

Im a trying for the moment like that but it is not working: 

= IF(CALCULATE(COUNT(vw_vision_activity[Category]),FILTER(

 

Can someone please help me or tell me which formula use ?

 

Thanks in advance.

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Aikeno ,

    You can create a measure as below to get it, please find the details in the attachment.

    Test done = 
VAR _projectid =
    SELECTEDVALUE ( 'Project'[Project_ID] )
VAR _vdv =
    CALCULATE (
        COUNT ( 'vw_vision_activity'[Activity_id] ),
        FILTER (
            ALLSELECTED ( 'vw_vision_activity' ),
            'vw_vision_activity'[Project_ID] = _projectid
                && 'vw_vision_activity'[Activity_category] = "VDV"
        )
    )
VAR _closedvdv =
    CALCULATE (
        COUNT ( 'vw_vision_activity'[Activity_id] ),
        FILTER (
            ALLSELECTED ( 'vw_vision_activity' ),
            'vw_vision_activity'[Project_ID] = _projectid
                && 'vw_vision_activity'[Activity_category] = "VDV"
                && 'vw_vision_activity'[Activity_state] = "Closed"
        )
    )
RETURN
    IF ( ISBLANK ( _vdv ), "No VDV", IF ( _closedvdv = _vdv, "Yes", "No" ) )

    Best Regards

    • Aikeno's avatar
      Aikeno
      Icon for Helper I rankHelper I
      2 years ago

      Thanks for your help !

       

      It seem to works for you but on my side it does this:

      I assume that i should have only one row for each project. It is the case when I delete the measure in this table.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Aikeno ,

        Could you please provide some fake data in your tables (exclude sensitive data) with Text format and your expected result with special examples and screenshots? Later I will check why the formula is not working in your side and give you a suitable solution. You can refer the following link to share the required info:

        How to provide sample data in the Power BI Forum

         

        And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

        How to upload PBI in Community

        Best Regards

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Aikeno , Try a measure

     

    Test Done =
    VAR _TotalVDV = CALCULATE(
    COUNTROWS(vw_vision_activity),
    vw_vision_activity[activity category] = "VDV"
    )
    VAR _ClosedVDV = CALCULATE(
    COUNTROWS(vw_vision_activity),
    vw_vision_activity[activity category] = "VDV",
    vw_vision_activity[activity state] = "Closed"
    )
    VAR _opendVDV = CALCULATE(
    COUNTROWS(vw_vision_activity),
    vw_vision_activity[activity category] = "VDV",
    vw_vision_activity[activity state] = "Active"
    )
    RETURN
    Switch( true() ,
    not(isblank([_opendVDV])), "No",
    [_TotalVDV] > [_ClosedVDV], "Yes"
    )

     

     

    The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.