Blank Values when multiple filters are applied
Hello - I have a simple dataset that looks like this: Category Sub-Category Class Value XX Value YY AAA X1 A 1000 2000 AAA X2 A 400 2500 AAA X3 B 2000 300 BBB X2 A 5000 1000 CCC X1 B 150 1200 CCC X2 B 350 10000 CCC X3 A 300 800 Then I have a summary table that looks like this: Category Value A (=Value XX/2) Value B(=Value YY/2) AAA 1700 2400 BBB 2500 500 CCC 400 6000 Then I have two filters, one for Value A (Filter A) and one for Value B (Filter B), that are the result of the concatenation of: Filter A = "Class" + "Sub-Category" (ie. A-X1, A-X2, B-X3,...) Filter B = "Class_duplicate" + "Sub-Category_duplicate" (ie. A-X1, A-X2, B-X3,...) --> I created a duplicate field for both "Class" and "Sub-Category" to avoid interference between the two filters One of my goals was to apply Filter A only to Value A (with Value B not impacted by any selection) and Filter B to apply only to Value B (with Value A not impacted by any selection). I was able to accomplish this with the following measures: Value A = CALCULATE( DIVIDE(Value XX,2), ALL(Table_name, Class, Sub-Category) ) Value B = CALCULATE( DIVIDE(Value YY,2), ALL(Table_name, Class_duplicate, Sub-Category_duplicate) ) This works only if: I apply one Filter at a time OR if the selected combination of "Class"+"Sub-Category" from both filters is the same But considering that my summary table is at the "Category" level, while my filters are at the "Class"+"Sub-Category" level, the PROBLEM is if: I make a selection from both filters AND the combination of "Class"+"Sub-Category" is different between the two filters, which will return blank values in my summary table for both Value A and Value B. I tried combining different functions inside the measures Value A and Value B, but with no success. I hope I made it clear to understand, any recommendation/hint will be greatly appreciated. Thanks! Davide
Using LOOKUPVALUE functions gives only few records in CALCULATED COLUMN
Hi, Requesting help from experts on a typical problem being faced in using LOOKUPVALUE. TABLE A -Is a master file with all time stamps in seconds (from a specific start date and time to a specific end date and time) ,for a MeterID,SubscriberID,and need status flag from TABLE B from respective time stamp for respective MeterID,SubcriberID. TABLE B has a status on a time stamp only if status is logged in that time stamp,else there will not be time stamp record.There were no duplicate records in both TABLE A and TABLE B when checked for all the three columns combined (Date_Time,MeterID,SubscriberID) ,but status column has many repetitions as its a categorical type field. TABLE A (Change_Log_Master) TABLE-B (tmMeterReadings_StatusChangeLog) The DAX iam using for "Status" CALCULATED COLUMN Is Status = LOOKUPVALUE(tmMeterReadings_StatusChangeLog[Status], tmMeterReadings_StatusChangeLog[Date_Time],Change_Log_Master[Date_Time], tmMeterReadings_StatusChangeLog[MeterID],Change_Log_Master[MeterID], tmMeterReadings_StatusChangeLog[SubscriberID],Change_Log_Master[SubscriberID] ) But the result is yeilding only a few records in TABLE A , as below in the CALCULATED COLUMN and all other records are blanks. Being a newbie ,your support on this earnestly appreciated. I tried to concatenate three columns to create an unique ID in both tables as a workaround and use LOOKUPVALUE but no improvement.
