The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi - I have a single data table with the following fields for the :
ACCOUNT_NAME
PRODUCT_NAME
STATUS
DATE_GENERATED
And the measure I want to generate has the following logic: for the most recent month of data at any one point in time, COUNT the number of ACCOUNT_NAME where (PRODUCT NAME = "Bus" and STATUS = "First") AND (PRODUCT_NAME = "Car" and STATUS = "Second").
Any DAX support would be appreciated!
Thanks
Lewis
Hi @Anonymous ,
Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
Hi @Anonymous ,
You may create measure like DAX below.
Count ACCOUNT_NAME =
CALCULATE(COUNT(Table[ACCOUNT_NAME]),FILTER(ALLSELECTED(Table),YEAR([DATE_GENERATED])=YEAR(TODAY())&&MONTH([DATE_GENERATED])=MONTH(TODAY())&&(PRODUCT NAME = "Bus" && STATUS = "First") || (PRODUCT_NAME = "Car" && STATUS = "Second")))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quic
@Anonymous ,
I would try like:
Measure =
VAR _lastDate = CALCULATED(MAX(DATE_GENERATED); ALL(Table))
RETURN
CALCULATE(COUNT(Table[ACCOUNT_NAME]),
FILTER(Table,
YEAR(DATE_GENERATED) = YEAR(_lastDate) &&
MONTH(DATE_GENERATED) = MONTH(_lastDate) &&
(
(PRODUCT_NAME = "Bus" && STATUS = "First") ||
(PRODUCT_NAME = "Car" && STATUS = "Second")
)
)
Did I answer your question? Mark my post as a solution!
Ricardo
Hi @Anonymous
smth like
Measure =
CALCULATE(
COUNT(Table[ACCOUNT_NAME]),
FILTER(
ALL(Table),
(PRODUCT NAME = "Bus" && STATUS = "First") || (PRODUCT_NAME = "Car" && STATUS = "Second")
)
)
Thanks - this workds perfectly - how would you incorporate 'for the latest month of data' into that though?