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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
EricHulshof
Solution Sage
Solution Sage

How can i get the correct values?

Hi, Sorry for the vague title, but im not sure how to rewrite it.

 

The sample dataset to explain the problem looks like this:

Project    Active   Active from   Active to

Project 1021-5-200725-01-2008
Project 1025-1-200812-09-2009
Project 1112-9-200931-12-2100
Project 216-10-201931-12-2100
Project 3015-4-201017-08-2010

 

The idea is that i want to see all project have a 0 but no 1. in this example i only want to show project 3. As project 1 got a 1 in 2009 and project 2 also has a 1. 

 

So the expected result should be:

Project 3015-4-201017-08-2010

 

Please note that projects can have multiple 1's and 0's 


Quality over Quantity


Did I answer your question? Mark my post as a solution!


1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @EricHulshof 

You could create a new calculated table with only the rows of interest or do it with a visual  and a measure. Let's look at the latter:

1. Place all columns of your table in a table visual (make sure they all are set to "Don't summarize"

2. Create this measure:

ShowMeasure =
VAR Count0_ =
    CALCULATE (
        COUNT ( Table1[Project] ),
        Table1[Active] = 0,
        ALLEXCEPT ( Table1, Table1[Project] )
    )
VAR Count1_ =
    CALCULATE (
        COUNT ( Table1[Project] ),
        Table1[Active] = 1,
        ALLEXCEPT ( Table1, Table1[Project] )
    )
RETURN
    IF ( Count0_ > 0 && Count1_ = 0, 1, 0 )

3. Include  the measure in the visual filter and choose to show when it is 1 

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

  

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi @EricHulshof 

You could create a new calculated table with only the rows of interest or do it with a visual  and a measure. Let's look at the latter:

1. Place all columns of your table in a table visual (make sure they all are set to "Don't summarize"

2. Create this measure:

ShowMeasure =
VAR Count0_ =
    CALCULATE (
        COUNT ( Table1[Project] ),
        Table1[Active] = 0,
        ALLEXCEPT ( Table1, Table1[Project] )
    )
VAR Count1_ =
    CALCULATE (
        COUNT ( Table1[Project] ),
        Table1[Active] = 1,
        ALLEXCEPT ( Table1, Table1[Project] )
    )
RETURN
    IF ( Count0_ > 0 && Count1_ = 0, 1, 0 )

3. Include  the measure in the visual filter and choose to show when it is 1 

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

  

Thankyou, i used your code to create a calculated column instead of a measure. this worked better on the whole dataset with relationships e.d.


Quality over Quantity


Did I answer your question? Mark my post as a solution!


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.