Simple thing.
I am creating virtual table in DAX with filtered data which matches some criterias, then I want to display that data by campaigns, but
in Pivot I see only the particular date I have filtered initially, despite the fact that later I do obtain virtual table with all possible campaigns.
In DAX STUDIO I see that table, but when I create measure using that table in EXCEL POWER PIVOT I see only that campaign I was filtering for...
I tried all stuff I had in my mind but now stuck,what can be wrong...
TABL= just selects data from initial table and breaks lineage for me later to be joined with the list I need.
test - is the list I need. So it represents list of customers on particular campaign. Then I want to see how they perform in future campaigns and I do naturalleftouterjoin with TABL by their ID and DATASET.
Then filter those which I need. THEN use IN DAX measure and I see the table I need- All campaigns, and all customers placed order in particular campaign.
After I copy all this data in EXCEL POWER PIVOT (green window) create measure and want to see it via PIVOT TABLE I see only that campaign they placed order, all other campaigns are not visible.
evaluate
VAR Tabl=selectcolumns(TABLE;
CUST_ID;TABLE[CUST_ID]+0;
DATASET;TABLE[DATASET]&"";
CUST_TYPE;TABLE[CUST_TYPE]+0;
CAMPAIGN;TABLE[CAMPAIGN]&"";
SALES;TABLE[SALES]+0;
ACTIVE;TABLE[ACTIVE]+0;
LOYALTY_GRADE;TABLE[LOYALTY_GRADE]+0;
TAXES;TABLE[TAXES]+0
)
VAR times=SELECTCOLUMNS(FILTER(VALUES(TABLE[CAMPAIGN]);[CAMPAIGN]="2020-04");"CAMPAIGN";[CAMPAIGN]&"";"CAMP_ID";"Y")
VAR test=FILTER(NATURALLEFTOUTERJOIN(Tabl;times);[CAMP_ID]="Y")
VAR LOYALTY_GRADE_APR2020=FILTER(test;[LOYALTY_GRADE]=1)
VAR list_apr20_a=SELECTCOLUMNS(LOYALTY_GRADE_APR2020;"DATASET";[DATASET]&"";"CUST_ID";[CUST_ID]+0;"STATUS";"APR_20";"CAMPAIGN";[CAMPAIGN]&"")
VAR list_apr20=SELECTCOLUMNS(list_apr20_a;"DATASET";[DATASET]&"";"CUST_ID";[CUST_ID]+0;"STATUS";"APR_20")
VAR temp1=NATURALLEFTOUTERJOIN(Tabl;list_apr20)
VAR temp=filter(temp1;[STATUS]<>"")
VAR result=calculate(sumx(temp;[SALES]))
return
calculate(sumx(temp;[SALES]))
