Forum Discussion
creating a dynamic table in Power BI
Hi Experts I am trying to creat the following in Power BI for the Y Values (dates) as shown in the image below. The image below is an example that someone has done in excel. The question is how woul...
If the error message is about SUMMARIZECOLUMNS and ADDMISSINGITEMS not being allowed in the current context, that's an issue with SUMMARIZECOLUMNS not playing well with slicers on multiple columns.
I re-wrote the measure to use the old ADDCOLUMNS style pattern that was used before SUMMARIZECOLUMNS was introduced:
MK-Stat = VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT))) RETURN SUMX(ComplaintsByFiscalMo, SUMX(ComplaintsByFiscalMo, IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]>EARLIER([CountComplaints]), 1, IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]<EARLIER([CountComplaints]), -1)) ) )I've also re-uploaded the change in the .pbix file on Google Drive. The same link will download the newer version.
- Hi thanks for the excellent feedback. I'll have to post another question as I have just applied your calculation in the SE for and the Z test as per link and I am getting a different p value to the one showing in the main fact table in column mannkandelY. See what se and z test values u get as per link.
There we go. Now that I know what values you want input into the MK test, we can use it in the context of your report:
MK-Stat =
VAR ComplaintsByFiscalMo = SUMMARIZECOLUMNS(PMS_COMPLAINT[FISCAL_MON_START_DT],"MonthGroup", SELECTEDVALUE(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", COUNTROWS(PMS_COMPLAINT))
RETURN
SUMX(ComplaintsByFiscalMo,
SUMX(ComplaintsByFiscalMo,
IF([MonthGroup]>EARLIER([MonthGroup])&&[CountComplaints]>EARLIER([CountComplaints]), 1,
IF([MonthGroup]>EARLIER([MonthGroup])&&[CountComplaints]<EARLIER([CountComplaints]), -1))
)
)sorry to be a pain
but i cannot get this to work, can you kindly upload the pbix, please.