Forum Discussion
Writing Dax Measure by comparing 2 columns from different tables
Anonymous , Seem like very similar to HR
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970Or the attached file
Hi Anonymous
I don't know if I understand your needs, but you can write a mesaure like that:Measure =VAR __DATE = SELECTEDVALUE(d_date[data_key])VAR __CALC =CALCULATE(SUM(f_storage[Weight]),ALL(f_storage),f_storage[data_in_key] < __DATA && f_storage[date_out] = BLANK())RETURN__CALC*If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kind Regards,
Marcel MagalhãesThnx marcelsmaglhaes amitchandak ,
The use of Crossfilter and selected value both worked.
This measure worked,var dateshow = SELECTEDVALUE(DimDate[DateKey])returnCALCULATE(SUM(FactStorage[MaterialWeightKG]),CROSSFILTER(DimDate[DateKey], FactStorage[DateInKey], None),FactStorage[DateInKey]<=dateshow && (FactStorage[DateOutKey]>dateshow || FactInventory[DateOutKey]=BLANK()))
Super UserHi Anonymous
I don't know if I understand your needs, but you can write a mesaure like that:
*If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kind Regards,
Marcel Magalhães
Hi marcelsmaglhaes ,
Thank you for your reply.
It is not working becasue if i use this measure _Date =SELECTEDVALUE(DimDate[DateKey])
Then it shows a cumulative sum of the DateKey something like 20.3 Million. Its not showing exact DateKey 20230112, if i have selected 12.01.2023 as a filer from Dim_Date.
The main goal is :
If i select 12.01.2023 as a filter from DimDate, then i need to sum all the dates where DateOut is blank till 12.01 (also 10.01 and 09.01).
That means the sum should be 300KG ( Material B&C as they dont have DateOut). The DateOut key by material B is written by mistake.
Do you have any other suggestions? Thanks in Advance 🙂
- marcelsmaglhaes
Hey Anonymous ,
If you need to filter a date, then selectedvalue captures the selected date and will serve as the date to look at the fact table to search and filter the values. Of course this measure needs to be improve according your visualisation needs, but is the starts.
Thnx marcelsmaglhaes amitchandak ,
The use of Crossfilter and selected value both worked.
This measure worked,var dateshow = SELECTEDVALUE(DimDate[DateKey])returnCALCULATE(SUM(FactStorage[MaterialWeightKG]),CROSSFILTER(DimDate[DateKey], FactStorage[DateInKey], None),FactStorage[DateInKey]<=dateshow && (FactStorage[DateOutKey]>dateshow || FactInventory[DateOutKey]=BLANK()))