Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
User wishes to select a node and filter all visuals for data at the selected level and its child nodes.
For example, if Location 1.1 is selected in the filter, all nodes in the hierarchy starting from 1.1 (like 1.1.2, 1.1.1.1, like 1.1.1.2 ...etc need to be selected)
* Using hierarchical slicer/ list filter with hierarchy is not an option
* There are 6 levels and master data is extremely large. Using Power query to do a self join is not feasible.
I created a measure that validates data with only one Location being selected.
However, I m unable to get it working for multiple locations.
Need help with the Validator measure.
Table Amounts:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVxLlvamDtzLN84ABAg0vEl2kZP9b+Pakm2qbGWQ859uAhZ6lB5I/c8/Pxm/P35rjnn887/jPyl1lfb7949/frUcP0+VhkvDl5qcu5aUZ+lYLL4UB47WcdeMpfO81Suu9Fg5f6c6Gp4nQYUeP5uJ7V12UdHPT/XjFwmB5+dNOl0rDvT7HCuwZBftXXzXUiSj+tL5K61rfGlvTsVxBp4Xn/Krtj6J9th1/kpMkPRrkzO1FiICLzysrC994yS9t9q+n6rOdysloT1EXOz35fvJwbYakXFJ6/zfR8EDT5puNol1lOMlfBeFtYqfuhStnfdpKooEWnzq/F0TQzleN+7nLps90Vwn2tpE9bwOjKXR1u+jGO2k2nR0vNb1rfN3aiTjixmnSFrtpO51c3Co6u/DJlcMq86nP/lact6nzbWepYeFK4Q1cOUi3VzR3Cyf8wREvJwXfzLp7fzU7Gq4dF34PK9ItsnVc7nR3VTc5uOki9/gIXBs9RTeVUDR1nK2v3Y5m+YEMh59d5iRXunAtQ28WiPiY+lcadbgwo8luOYuqbDpupYbeFUtuEuD9vOq9bAS/FQIazjf59SvHN2MrbWBu+LG0sP4BXfphrQ+cdetM7602qxI4dgGJAcP8UADpRnMwgHG34wObNsUxizze6DD52jafx/G1+VkVOJG2wo/R8/EJU5hX997uXOq0ulABbUeE4Vya40jzfH/Ig8v5XXrl0W6sTaAimr7fUQZS60bLtXHWmsBAh9Qc+5qqST/umVyaE350u4mdFCLGnox49x0eM/1+3Aw3EIntUbSZ/jct/gdJIvIl/bmLm2Ifml3kXQtJK25/ftYk2x8bfA/zISkdYnEZTzZTgI0hiPDqgkZl51MAo2yXUbXlgjSzXWMRogMu6QRhbfuunuag8SvW+PHYKVpmxsHgSQvBBTrhDUTNF4rUngbnn/rAHm8smxM7m0k1uVLuhoa+Q0ozTVqohO61bC6RhkdaNuSj13y+2ioX9mMYIgcVB2JQ2nhCyuK8tYoN5QIyJ6lK945T5KKMLTQa6wGHmpHSW7/VWkJjFJW1++uHoGwoQJUMAeL0OVRbNnEVxOS8trwajIJNipEUIvxum15jWGkUZfafMHhCfD9QCvre6ATP4ogGt664d7rFVCMrYcH5mUHetQ9hJZ0m7nqIscm+8A1GykA+IZROkq5AIVVGL50o40cEWpiy05hJXO4eBi7piZxiCuFtko6r6Abpc0vN9wpN4KN2xqGn6eJvTqWV12EKLqdl1hmQ05gb3N9meG8m2ORQtk2Shk14dMVd1M4dIvrpN0a2WTdQHmwk8CrP7zoy1D6lzrN4JL+Byt0aQJd7hIOgsjVNDKg8t0VkYb1+b3UdeBIPINHE7Vj/HyL8VTOioHLveJIfGRcCXBFdFLYL8gGrolIuF2GK1NdJCrbWN1GSwj00Fobe+S+DUFlJDGIY0klt3CDk2fB1khYFyo4fM5BzqRsIGwYTG6ve1649sQaXdlH0UTZnebWhNQMcgkdRoEwJCeTwO7G93AYFaHE8FvSRL5k9EiDS5KcODPWogOvpQDqkoXjjnWTbnxDk59X2bHWfWMTdnZAhVVOGGQHeIfq6pcMRzLRkcC7f8SU9PNiRriSWuxLvCNkWyzkHT2vVwByBQUOGFUQ0G737jC42LYmmMJSvFYF1a3VI/+/bir6hoUeZZq/WJBOxhHSTlyCSG00T+GfpbF9nUVu8nxrPDdWabTpQiAnsJt9qXA2LQ95XyseJnRxBf7rJX0vcxWn5V5CDvaA8Ic+8BY9SmrPgeD3rXdiE9QY6uG1cFff17qy8Wepbubaaglz/VvzgBNYukHcmdvwXk/s5JoRZc5nF8QYRx5EB4LqtlJw6c7hHO+GdPjWvcuT5+pJ5kMGaKGE33oObJtRhxjgQEM3E37/xd0LWifJ5HILJ31lEXPnDhYtHMZL3x2WZni0l/ijmrVGxQMF9H1V2rX9YOuakH6SPAofByW6aWrfL5lrp6L1oJc+4jqSIhR8j8geLeFe8k9F0P/a5Qe2NQVJ103gWpNMdT4Ejr7IfNp2dTJQVDfX3ZMc1pMY3fQv9flduSujJEXw7b3ThS/eekK1+vp9LcTxB7WWU75mhve9PZ3HJcXI5K7g4+d+hKTY9n0P/SM8u9I6d6oT4WJhPtUQ63ZJPwzfk6aXnbofO3SpfslwD1KXkPHYTppESiIsJ0NexEPyPjtS+BwYOk3qeduw40+EuS8YvKo3cK/nyq4s0kpiqW5zgyzr9nROe5/191FPB8gjxSXa5waZWVvDpbGzlaZS8FprM+MQCRG49i7TSRSWh/aDgStZOZeOIDJRavfSR4CR6WfwnQzydnVRd1iJ+3EYPACN3A+YSR9GCK5brUfUbl+4726il05L4H7EGh0ISZjNlnkLv9ciL3gbgxPfB6FThyhtCqn12mGfmcl3lxcrdGaGfELGNFSMO//x8L0vskiQvlYlJdQdAjV2t7oN8nAXpINQ/GqyEok42+eqxPYO6YCyrWLxu1fk0q0zHjdLSzyds1YowLiNxOG4TgKuBTo4O7EJ6+xqif+JwGkKRn2313JTGBx7ABKOOteXdr/VGInte+Q5Bt0Xy6x10YXXTpo6hk2UQA40x4dLUSJuhZAfUuaOXN+hsVc+X74dnt9qUfku+UnaF3qte8lDWR0JhnsltShSQbkqq9nYpJvQre4s4ZTH9ZD2wp8I+Avv6tvhHs4ii9Ccg2aUDNjD9tay+DzAPd5eX7wN1xmVipfwvZo3TRLeuqVOsSROdJekS5KYL0IWI5u7dsVbbkO4wIc54XAQXkQOv7S+RET1QISUCWC/l5o5aad8zC8r/Dy1iaxA3mpvmiy54Rd2nLoNwWonpw+gNVYh2tdDoMWjx+tWzqUxJjlOUEFblvi5qHnXRVTogwltCmaPN+nnpZaQ52n7uH7+9IHb0OhW6UtQsJuYSHNjwFqCXgmtu/e1vgR6NccqKeB+xh+TU5i+EWFJS/yfq+syoeh37uDS6sgwyy3EauJ6XJk6hVq3Rkc6QpHR7a9+z0Pzy6ociI+gkxxF2fAtc9GXGvFvfonwC0uUBl9JgsRL6KRb9X3g4dZJY7BHoo+kFBFPxr0mQBLIjtb4FJviZaVn4X48C80kwqlXiNO+n3Lp11mSa0kUGzlYBRU8gl/9fqtHsDoTFxhZR1n9P3ZJoyDnrqG66XcOFHZJySqHzO3BRxkEP3fk5m6u9sTmwjdaSZY8ZCIQXBi5KdY1tg565mPkUbGpanTWJsBHLaSDd6YS9W6ulIBLvR403qGvm8LIdMblOF4RLlZXS/uvKFG6kDrBK+ORZY3vUjwXCeKgUXGjMXuvNMuTDq2J9N39HFlMUufxzKK93AW0mczFmSoUZrQaYQY8M4/C7gIobLZIlPLoZ3vFA/AAfWBQkhU58h8JWBaTxhMes7BurTkSQWRh3VSMV+ENlOagj3w71oDKyFIpr2oL+3Zg01LJgiOPCBpXvSbEObMlQaRnCdVGkuE4LhyQQag70BgGpWD4Gi9krrc+/bzHLIE79wpHqP2lL2oscyXw6UnHHElp636XTGLSaAgqiZuWqHcvihPRHgvHzPacp2v275dc6BbtFi8HefVvpFJ0J6M1S+hcY3QktZJojqOs8nYkV/RLbh+eLVrL0sp69bkkFc9IbmcWyUZD0NQs43QxNg4koNzQR+bqXJcW5VJPjcqJL6ycUDdcjMZXMhrvdOz3oQLYh1AQVB/mHnFippzRvTMpwYFkeXUK+m6vFfF5VpQLAlujJcyYVklkHAWbMROAjKKXcKV5bOd5eP0EtiSYy/reNih0Yf2cjxGvKUmO6PHgKMTbBQBZGSDbVplWOeKHXao9kWNkU2MmCO4aUVWSpN3fmLRnUWmUNVmb9AmoFz77PBKWaBIRMoT5xNoydCaXioISL0GGc6h3+34qakPSkjQrCoO9J6Z/td+3JACOpi6ikNT9iHIJgGyzqY2ZJILR4UjpxVOXcfSMTpCXyniJUQa/MVXItKj2QgXU1pOQKt5NlWv1UAJqgzEcWiPGq1YPTfEDo+Yd9P2ebvQXhEe+PG19bxU53SC8gAf1bppEOZEJUonqaTnyWAsjz424XsWXNGBxnali3wPjjcQmxcaXUkcj4MhSEo88xZL4IhTDWqLVNZ7NWQlty1hUEr4HbElW/pXQpuRVyF8ZumW5agToRqF23UtSLQkwwnUWGd8Do73dRha7e6g9WvneKnBaRpY0hcPlegmUUG1UOlBAWphbPq7TvbRpEnhGbbBwCRVas45U0L439luJZq7zKgQkz/pXF9NKkumIBluWZ9ewBFqBIv4RASX1q6gP4XvGzi3L9fNXcd3RjSy1DFmtSjYC7uwII5KMKSLFxp0b0PXYKpVS7vYhv3AngLSNPy8ioA9oWZ9fItwlKIMC9uaMwVzaXG9Cxe6LPK8CdC6gDgASLSW5rjtimYmdhlYov7RvhyB9UOABSHfkRJRyCAQyjaqQtwuMEJerhmvLo2v2ShNDTdqTbPnq59FMzfwdjr0jtllww8TTrNACE7Kq18+DnPOfv5m50SAS0fTf96cAtKy6Xjy7oEil8TR+L2Ej75xO57MLmptq5I7Pku0Drxbqh4wdaVdzwP37xQvXzhg1ec7b1UtRB9C/X4bvKhPNIy/6zk2rAnn7oX2F4hpuArTo0UP52uXx4eF5FJegm7zGy+PDQEgRu/REWJEVRbvZwwuc/tCFS3fg6abVFYWF7R691fL9VnQJiFYgnqYCA0teOhNVCjX9ciM617t1PBCqFLXV/vsI8mrIXsSoAmmCtPklPkKqWvqXjPsFVJNvRZFyoZRvrY6WmTF+H4WK1kZ1Ql9affFQSHdBDUdMazy76O2eNRQ6I1Thyttrxa4yv7tiwFOs/b56+PMnS0MCMduPyscjLsgGplZcwqdnmSZfAl1dlnRagl6qPltirldSMolPMFzTCykvJmhHFlvwXvBuLtHJ/dLrmE9ZpFC3Ufq3bBF3ZScRc2WAElONgchvRjkPZZLK7xdhqY1WsFEgBjIy6zo0AyZQqe1x9UUT1/CiMCLgek0FR+WwuCRfE9dhQpFOP7PJFXYJDd1ib/gM7r4mmiUaArIxY2/eWJbM3F4tm4VGiSFiaAqf4uZgazhz+7gTf1OwRbPEUCqfqwsSODZ3++JZYuim0nj0eLMweopr/zLDnVZfOMRNLZErSuUvblzzjkD8Vg3HyQ48ZHOtgye1y4MMfXVSGsjCuxWaWodQ/HoNe83Oe1XMSMSoMxIhzetTMQaNioZPQ4as3RFN9IEpiRHUvcaoxiOrsl2rFh6rhpRP25CcChEcDDBIB2pXamoHYLUOE0Hbv0dJ2WiXgukLz+LsZHV0mjPAPkp9jbOtjVoqNMBzV0ViWKzQVAgQqK/5aIB3WyMZCY6cvmNvPfZ6NaGJYPS4R/BJ41aggGPxefAu6EL4jH7EMNsgNgFAqsGsOJdKa8EpzReewYVZo1s8NbwHOJ0MVIwHEzwDb5WUafdLafjvl5rF3KTyDJmA14/C+4tLl5nyaC+4x1qBil3ciqZ7GgfElvbDDpK/POC6smY2clODuZOGoy9Pd71A0S4Y7GuveREYtx82Ufp3Ef0UlvK8GhZgBHSag4/+Gt+X7Ueq8NASTNuviRNmzwtp9EPytO3YPnDyQMttkI5NapLsivmjkSCGA27FWWbqatdoG35/6hoyIdo3B0VxMtIwYzkccTKh5+7bVjLVF16kM17U7QHX0gSAXCEOZzu/pEfXPf9ZBxxkNMsmeFxjdJDGIMpUuPA2YpdwKQQYE6JBRfB8+o6imNIT4I9RYJ5lw45stOLt6NxUe7NkKfqsjD6Fs0KVZoup0aEWujGA3RDFJXzXaD2bIL97B/9j5q/WmlhqjFxMGs+Hkths2d+qGG/Wcm5pmuKPa+dEBH984Ek4/+WTWzk9mrZGTILCuxSaBsNWbYbwp1vXExIzdEx3zS4qOvT3CGB6Z81Cm3SLSk0TDfTMYvXMGq+3H1IYqMhfvUpvgZwri4f2x4Z2fRlj236pVxbw2CYnc5UvFdcfI2hJKHP9mSdLDDWipkajqlh6tcH4AyYyBn+rbXOs0TTxinKisVVIm27pB7bztPzaIp5SM1d8hXzJLi9Tdk2wM0a3LNHOmLO0nmxy/Bb+uzxA3sGy99zmv/8H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Location ID" = _t, Amount = _t, Product = _t, YearMonth = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Location ID", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Location ID", "Product", "YearMonth"}, {{"Amount", each List.Sum([Amount]), type nullable number}})
in
#"Grouped Rows"
Table: Locations
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZbZkqIwFIbfxeupKRICyp0CKptEEGTp6otWBxds941mHn5GQkK40CqL/zvbnxP8+OiAzq/WxxGK11zZRgtDeXQ+f3104PvB7+rx3/f3+/cN2MEGwX5uyXElEitRJf0La1FQhuHyZWjT625diVD9ANapsqW5W0qb+HsyHFQC6f2wSgVZKhVu8NiHi3XUdSqRXImqOCzV63GwForqDMcwqERdUjQtu4r2rnEiGKYBkKVv8b4S9qiwkVbid6250PeNuR2W8dyuxEoj5uUV8K79KlsvA4va84RIQ0DgCMgRsCbsU+rmen4duAlJAQBHiBwh1sSkF48WBpjuVyXxB0BGNBlg3ULkpLfrWZgdXD8jarFRcz1A1sNYu4WlJmArh1NCII7gMrAe9ortr9f+sx8cx4SQOELkCNpDkBZfWb5d2Re5rkrmCMQRqCZuIxwW4e5P/ND6hKAO04pg7bChmsl9GeiuA8hRANTipl/ILJ6riTZ9QOzgVd2twtQsMpvmNo2f6T2f+Y6ikd0QGjUXvZnmUvFO10Ifj9aKSQjAEVwGNk3fnLqWH04muCTThJAjRI6g07TADO9lKdmsXJUQIk+06hKryt7TjrTIOWuRF5Qvj1CoRcEWBWvKdTPvIO2uI/1kEEpqUa36qgrflCaplv16BrZ5r3siftOZQbafCQxjzUOTTJeJe7BLlY2WuWegazFayUU6S8jdAHtMDZmauveFA/WSpL6eDMkZgkqj5qI37pnSMJndBwY+ByW55gSO4DIw99Ifoz/aH8rnHCWEABwhcgR1bwnHR3w0sjRWiHsi5AjEEXQXNobvWpvnPZogcgrF+vZlFdFdcBb69ftyc/LLpr6mEVWyfptdcLY4WXelb/U4IneRKDE1i8ym6cpumBby3QqBRdRyo+aiN9N09K3hgelycZn4hOhyBJeBTdOTbM84PDaeNyb+ij2OEDmCTnMANF/PD3r4c3kSQuGJVl3NLoTCZTk7b9dm3COnDgktCrYougs/mRlqsn5ejqUToUCLatXHduFPiOOV8mVO/RXxBFUvsd/0vdfsws41/OmtULP0+CJKsVbWLz/m8+jnPPAQSKIonxElokoWtfE5LpzwlC2wp2XkzkMSU7PIzOdMKnVsSduVOiPvNCQ3ai5647PWKweOlKZBNL4QossRXAbm8/+zhMMdTgo8quvvcYTIEezOyzJX3b2ep2B6JITCE626Gp/jfOe58sKzVfhN/nAILQq2KOqz7i3w3MaTJKB/U0CLatXHfE7VhzmXQOnalksoyFGIY+he+05/gB1vHgbesPP5+Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Location Id" = _t, FunLoc = _t, Path = _t, Level = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location Id", Int64.Type}, {"FunLoc", type text}, {"Path", type text}, {"Level", Int64.Type}, {"Name", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"FunLoc", "Location"}})
in
#"Renamed Columns"
//This is a disconnected table which has the same data as Locations table
42
Can anyone help me with the required DAX?
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |