With existing measure count number of occurrences <0 and >0

[jprlimey]

My first post on this brilliant site, so apologize in advance if I’m not following the correct protocol…

I have the following visual

Where the measure Del vs Actual Days determines the difference between Delivery Date (the promised date) and Actual PGI Date (the actual date of shipment). The DAX for Del vs Actual is below

Del vs Actual Days =
CALCULATE(
DATEDIFF( MIN(SalesOrders[Delivery Date]), MIN(Shipments[Actual PGI Date]),DAY),
FILTER( VALUES( SalesOrders[Sales Document]), ‘SalesOrders’[Sales Document] = max(Shipments[Sales Document] )
) )

Now using this existing measure (Del vs Actual Days), how can we create another measure to count the number of occurrences <= 0 and >=1 to use in a card visual.

Thanks
J

[BrianJ]

@jprlimey,

Welcome to the forum – great to have you here!

In general, you’ll get the fastest, most specific solutions in response if you post your PBIX and a mockup of the result you are trying to achieve.

If your data are confidential/sensitive, here’s a short video on simple strategies for masking sensitive data to enable posting on the forum.

How To Mask Sensitive Data In Power BI

Developing measures for card visuals is interesting, because there is no evaluation context like you would have in a table or matrix, so you frequently have to develop that context via virtual tables within your measure. Here’s a thread discussing a problem similar to yours, with a solution that I think you may find relevant to your situation.

Using "All-in" Date Harvest Approach with Cards

I received the following interesting question regarding the Date Harvest Deep Dive video I did recently for the Enterprise DNA TV YouTube channel as part of the ongoing time intelligence series that @Melissa and I have been doing: [image] Billy is correct that the approach certainly is easier when you have an explicit evaluation context on date in the visual. However, for visuals that don’t contain this explicit evaluation context (e.g., cards, gauges, etc.), the approach absolutely still wor…

If this doesn’t resolve your issue, please just post your PBIX and I’ll be glad to work through a more specific solution with you.

I hope this is helpful

• Brian

[AntrikshSharma]

@jprlimey

how can we create another measure to count the number of occurrences <= 0 and >=1 to use in a card visual.

Something can’t be less than 0 and then greater than 1 at the same time Are you aiming for an OR condition?

[BrianJ]

@AntrikshSharma,

I interpreted that @jprlimey meant two separate card visuals, one for each filter condition.

• Brian

[jprlimey]

Correct, I would plan on 2 separate visuals, with 2 DAX measures for each condition >1 and <=0

J

[AntrikshSharma]

Try something like this:

=
COUNTX (
    SUMMARIZE (
        SalesOrders,
        SalesOrders[Sales Document],
        SalesOrders[Delivery Date],
        Shipments[Actual PGI Date]
    ),
    [Del vs Actual Days] <= 0
)

[jprlimey]

Okay with the latest DAX measure suggestion I have a further problem.

The Sales Order field in both Shipments and Sales table have an in-active relationship (many to many relationship), as in some cases we would have multiple line items against the same Sales Order.

The error message is per…

I’m assuming something else is required, perhaps i need to use the USERELATIONSHIP, but that doesn’t sound right?

J

[AntrikshSharma]

I believe Shipments table should be a dimension table with a column that has unique values and this table should be on the one side of the relationship, do you have such a column?

Try to avoid Many to Many relationships and bi-directional filters, most of the time you won’t be able to figure out what is happening in the measure.

[EnterpriseDNA]

Hi, @jprlimey we’ve noticed that no response has been received from you since the 8th of August. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!