Format VAR output

Anything related to PowerPivot and DAX Formuale
WilliamHarper
Posts: 8
Joined: Fri Jun 19, 2020 6:26 am

Format VAR output

Postby WilliamHarper » Wed Oct 28, 2020 10:12 am

I am pretty new to VAR. Just today I started on the following & found out there are VAR name limitations such as no spaces, no special characters... But my question now is how to control the formatting of a VAR. In particular I have the following statement in the code chunk below: VAR ChiSQpvalueLTA = ROUND(CHISQ.DIST.RT(ChiSqLTA,MAX(1, dfChiSQLTA)),3)
This is returned using VAR ChiSQpvalueLTAadj = IF(ChiSQsigLTA <> "<A Insufficient data",", p-value = " & ChiSQpvalueLTA, "")
RETURN ChiSQsigLTA & ChiSQpvalueLTAadj
But I am not getting the 3 decimal digits I want. I am getting either no decimal digits (if close to 0) to just 2 decimal digits.
Thanks for any guidance.

ChiSQLTABoth =
VAR JointLTAtodisplay = round([JointCountExc] * [LTALH], 0)
VAR ChiSqLTA = VAR VirtualTable = VALUES(Table1176[Grade])
RETURN
SUMX(VirtualTable,
If(JointLTAtodisplay >= 5, (JointLTAtodisplay - [ObsCount])^2)/JointLTAtodisplay)
VAR dfChiSQLTA = VAR VirtualTable = VALUES(Table1176[Grade])
VAR Interim =
COUNTX(VirtualTable,
If(JointLTAtodisplay >= 5, (JointLTAtodisplay - [ObsCount])^2)/JointLTAtodisplay)-1
Return if(HASONEVALUE(Table1176[Grade]),blank(),Interim)
VAR ChiSQcritLTA = CHISQ.INV.RT(0.01, MAX(1, dfChiSQLTA))
VAR ChiSQpvalueLTA = ROUND(CHISQ.DIST.RT(ChiSqLTA,MAX(1, dfChiSQLTA)),3)
VAR ChiSQsigLTA = if(ChiSqLTA >= ChiSQcritLTA, "<A Significantly different", if(dfChiSQLTA <= 0, "<A Insufficient data", "<A Not Significantly different"))
VAR ChiSQpvalueLTAadj = IF(ChiSQsigLTA <> "<A Insufficient data",", p-value = " & ChiSQpvalueLTA, "")
RETURN ChiSQsigLTA & ChiSQpvalueLTAadj

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Format VAR output

Postby MattAllington » Thu Oct 29, 2020 6:48 am

Hey Bill. I can’t see how this is related to Variables. Have you checked the data format of the input data and also the measure itself?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

WilliamHarper
Posts: 8
Joined: Fri Jun 19, 2020 6:26 am

Re: Format VAR output

Postby WilliamHarper » Fri Oct 30, 2020 1:37 am

Matt,

Thanks for the reply. I have taken a different approach and dropped all VAR and gone with a zillion measures as the code was not doing what I hoped (the rounding issue was not why). Below is how I envisioned the rounding being tied to VAR

VAR ChiSQpvalueLTA = ROUND(CHISQ.DIST.RT(ChiSqLTA,MAX(1, dfChiSQLTA)),3)
VAR ChiSQsigLTA = if(ChiSqLTA >= ChiSQcritLTA, "<A Significantly different", if(dfChiSQLTA <= 0, "<A Insufficient data", "<A Not Significantly different"))
VAR ChiSQpvalueLTAadj = IF(ChiSQsigLTA <> "<A Insufficient data",", p-value = " & ChiSQpvalueLTA, "")
RETURN ChiSQsigLTA & ChiSQpvalueLTAadj

No rush. Thanks in advance for insights. I still have a lot to learn about VAR - perhaps this is an area for expansion in the next book revision and maybe another course video.

Merci,

Bill

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Format VAR output

Postby MattAllington » Fri Oct 30, 2020 4:12 pm

I can’t really understand the point - sorry.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: Google [Bot] and 1 guest