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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
malex30
Frequent Visitor

I want to count how many 'Complete' Days I have that meets TWO requirements

Current Fomulas:
Once filtering by  'Contact' (Table)
HIT Card F Ct =
COUNTAX(FILTER('HIT Card','HIT Card'[Complete]=FALSE()),'HIT Card'[Complete]) -- counting total False rows

HIT Card T Ct = COUNTAX(FILTER('HIT Card','HIT Card'[Complete]=TRUE()),'HIT Card'[Complete]) -- counting total True rows
Hit Card % =
VAR TotalCount = COUNTROWS('HIT Card')
VAR TrueCount = [HIT Card T Ct]
VAR FalseCount = [HIT Card F Ct]
RETURN
IF(
    TrueCount > 0,
    DIVIDE(TrueCount, TotalCount, 0),
    IF(
        FalseCount > 0,
        FalseCount & " incomplete",
        "N/A"
    )
) -- Finding a percentage of True over TOTAL
malex30_0-1697648563970.png

This is currently giving me exactly what I'm looking for.
However, I would like to create a formula that finds the total COMPLETE Days over total days with a record. 
Complete Day = Two Entries per day, each entry equals to TRUE
e.g.

malex30_1-1697648705841.png

I would like to see another table that show 
Sunday, April 02, 2023 = FALSE -- because I have one True and one False
Tuesday, April 18, 2023 = FALSE -- because it does NOT have at least two entries
Every other date shown in this table would show a value for TRUE.


Please let me know if you need more information!
Current data model

malex30_2-1697649077037.png

Calendar is filtering Hit Card  (Calendar[Date] -> Hit Card[Date])
Contact is filtering Hit Card (Contact[Id] -> Hit Card[Supervisor])


A Formula I have so far to fix this issue.. However, I'm not sure If I'm on track and I keep getting this error.

Complete Days =
VAR SelectedContact = MAX('Contact'[Name])
VAR SelectedEmployeeIds =
    FILTER(
        'Contact',
        'Contact'[Name] = SelectedContact
    )
VAR FilteredHitCard =
    FILTER(
        'Hit Card',
        'Hit Card'[Complete] = TRUE &&
        'Hit Card'[Date] IN
        CALCULATETABLE(
            SUMMARIZE(
                'Hit Card',
                'Hit Card'[Date],
                "EntryCount", COUNTROWS('Hit Card')
            ),
            'Hit Card'[Complete] = TRUE &&
            'Hit Card'[Supervisor] IN SelectedEmployeeIds
        )
    )
RETURN
    IF(ISINSCOPE('Contact'[Name]), COUNTROWS(FilteredHitCard), BLANK())
malex30_0-1697743103195.png

 



@parry2k @Greg_Deckler @amitchandak @Ritaf1983 
@lbendlin @Idrissshatila @christinepayton @CoreyP 





5 REPLIES 5
lbendlin
Super User
Super User

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This is my updated formula

Complete Days =
VAR SelectedContactId = SELECTEDVALUE('Contact'[Id])

VAR FilteredHitCard =
    FILTER(
        'Hit Card',
        'Hit Card'[Supervisor] = SelectedContactId
    )

VAR CompleteDaysCount =
    COUNTROWS(
        SUMMARIZE(
            FilteredHitCard,
            'Hit Card'[Date],
            "CompleteCount",
            CALCULATE(
                COUNTROWS(FilteredHitCard),
                'Hit Card'[Complete] = TRUE
            )
        )
    )

RETURN
    IF(ISBLANK(CompleteDaysCount), BLANK(),CompleteDaysCount)



malex30_1-1697745818617.png

This picture above is me filtering the 'Contact'[Name]
As you can see my current formula is Wrong (slightly)
According to the table the Complete Days should be 3 instead of 4 

DateCompleteHIT Card NameName
4/24/2023 0:00trueHIT-19684Bryan Jasper
4/24/2023 0:00trueHIT-19685Bryan Jasper
4/25/2023 0:00falseHIT-19739Bryan Jasper
4/26/2023 0:00trueHIT-19775Bryan Jasper
4/26/2023 0:00trueHIT-19777Bryan Jasper
4/28/2023 0:00trueHIT-19875Bryan Jasper
4/28/2023 0:00trueHIT-19876Bryan Jasper



malex30_2-1697745961646.png

The second screenshot shows a "correct" number being 14 (even though for Friday, August 4, 2023 has 3 records)
This seems to be working because all are TRUE. 

DateCompleteHIT Card NameName
4/1/2023 0:00trueHIT-18380Anthony Winslow
4/1/2023 0:00trueHIT-18381Anthony Winslow
4/2/2023 0:00trueHIT-18431Anthony Winslow
4/2/2023 0:00trueHIT-18432Anthony Winslow
8/3/2023 0:00trueHIT-24132Anthony Winslow
8/3/2023 0:00trueHIT-24133Anthony Winslow
8/4/2023 0:00trueHIT-24172Anthony Winslow
8/4/2023 0:00trueHIT-24173Anthony Winslow
8/4/2023 0:00trueHIT-24211Anthony Winslow
8/5/2023 0:00trueHIT-24232Anthony Winslow
8/5/2023 0:00trueHIT-24235Anthony Winslow
8/6/2023 0:00trueHIT-24294Anthony Winslow
8/6/2023 0:00trueHIT-24295Anthony Winslow
8/7/2023 0:00trueHIT-24334Anthony Winslow
8/7/2023 0:00trueHIT-24335Anthony Winslow
8/8/2023 0:00trueHIT-24391Anthony Winslow
8/8/2023 0:00trueHIT-24392Anthony Winslow
8/9/2023 0:00trueHIT-24431Anthony Winslow
8/9/2023 0:00trueHIT-24432Anthony Winslow
8/10/2023 0:00trueHIT-24516Anthony Winslow
8/10/2023 0:00trueHIT-24517Anthony Winslow
8/11/2023 0:00trueHIT-24570Anthony Winslow
8/11/2023 0:00trueHIT-24571Anthony Winslow
8/12/2023 0:00trueHIT-24626Anthony Winslow
8/12/2023 0:00trueHIT-24627Anthony Winslow
8/26/2023 0:00trueHIT-25170Anthony Winslow
8/26/2023 0:00trueHIT-25171Anthony Winslow
8/27/2023 0:00trueHIT-25172Anthony Winslow
8/27/2023 0:00trueHIT-25173Anthony Winslow


Reminder:
Complete Day = Two Entries per day (at least), each entry equals to TRUE ( unless there is more then 2 entries, then as long as two are equal to TRUE)

@lbendlin 

That's not good sample data.  Ideally the sample data would cover all scenarios, including false and single entries.

You have both of those. The first sample has a false and happens to be a single entry as well. 
Are or can you help me with this?

Sorry, I had ignored the colored table.

 

Complete Days =
COUNTROWS (
    FILTER (
        SUMMARIZE ( Contact, [Date], [Complete], "ct", COUNTROWS ( Contact ) ),
        [Complete] = TRUE () && [ct] > 1
    )
)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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