Refer to attached PBIX file:
Data model mockup _ unable to count.pbix (219.1 KB)
I’ve probably been at the computer too much today. Issue is that I can’t come up with the correct number of “vendor names” associated with every PO Number – implicitly ignoring any blanks, as shown in this example. The correct answer (what I seek to show) should be “2” (not “3”):
The following DAX returns the correct result, but is far more complex than it should be because of some significant problems with your data model.
Count of Vendors =
VAR vTable =
CALCULATETABLE (
SUMMARIZE (
Merge1,
Merge1[PO Number],
Merge1[NRT Portal _ source data.Vendor Name]
),
Merge1[NRT Portal _ source data.Vendor Name]
<> BLANK ()
)
VAR Result =
CALCULATE (
COUNTX (
vTable,
Merge1[NRT Portal _ source data.Vendor Name]
),
ALLEXCEPT (
Merge1,
Merge1[PO Number]
)
)
RETURN
Result
In terms of the data model, I am having a hard time making heads or tails of what’s going on with that. At first, it looked like a snowflake structure, but the relationship to PO Stage Data runs in the wrong direction for that. Thus, the relationships are wired as if the PO Source is a bridge table, but that doesn’t square with the logical purpose of either table. In addition, if you use PO number from the dimension table in the table visual, it produces the wrong result.
Unfortunately, I think your DAX is going to continue to have major problems and/or require complex workarounds until you fix your model.
I hope this is helpful.
– Brian
Hi @mdalton2100 ,
Do you want to see the row in the visual that has blank vendor name ? Or is it like the visual that you have shown you want exactly like that only the row that has blank vendor name should not get a count?
Regards
Hemant
Hi @mdalton2100 ,
I am in full agreement with @BrianJ regarding the datamodel. However, other than brian's calculation you can try this one too.
Regards,
Hemant
Very nicely done. Which measure ends up being “correct” will depend on the result @mdalton2100 is looking for, but I think your measure also underscores the data modeling point.
@mdalton2100 - When I first looked at this problem, I thought the solution would look something like this:
Simple Count =
CALCULATE(
DISTINCTCOUNTNOBLANK( Merge1[NRT Portal _ source data.Vendor Name] ),
ALLEXCEPT( 'Purchase Order _ source data', 'Purchase Order _ source data'[PO Number] )
)
The fact that we both had to write such complex DAX to get to a simple result is typically a surefire sign that your data model is not set up correctly.
Hello Brian. Your suggestion is helpful. Yes, I do need to simplify my data model. The staging step is turning out to be extraneous and ultimately confusing to someone like yourself that is seeking to provide assistance for folks on this forum.
Anyway, to put it as simply as possible, I’m seeking to look-up (and count) those instances where the PO Table contains NRT values (embedded in the ‘NRT no.’ column) that match when looked up against the NRT table. Then, wherever the NRT values do match to the NRT table side: count the number of ‘Vendors’ associated with the matched NRT no. values … and by the same token, count matched values where there is no Vendor name at all. Hope that makes sense.
Thank you so much!